SQL is actually very poor at string manipulation, so if you have a way to do it NOT in SQL pick that...
However I show you here an option, but remember, that on large data it will be terribly slow...
DECLARE @VAL AS NVARCHAR(MAX) = '1:2~2:1,4,5~3:THIS IS A TEST' + '~'
DECLARE @ROWDEL AS NCHAR(1) = '~'
DECLARE @COLDEL AS NCHAR(1) = ':'
DECLARE @RET TABLE
(
POS INT,
VAL NVARCHAR(MAX)
)
WHILE (CHARINDEX(@ROWDEL, @VAL) > 0)
BEGIN
INSERT INTO @RET(VAL)
SELECT SUBSTRING(@VAL, 1, CHARINDEX(@ROWDEL, @VAL) - 1)
SET @VAL = SUBSTRING(@VAL, CHARINDEX(@ROWDEL, @VAL) + 1, LEN(@VAL))
END
UPDATE @RET SET
POS = SUBSTRING(VAL, 1, CHARINDEX(@COLDEL, VAL) - 1),
VAL = SUBSTRING(VAL, CHARINDEX(@COLDEL, VAL) + 1, LEN(VAL))
FROM @RET
SELECT * FROM @RET