Click here to Skip to main content
14,694,016 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
a string is like (1:2~2:1,4,5~3:this is a test)
and i want to split it with first '~' AND THEN the output string with ':' in sql

output like
1 2
2 1,4,5
3 this is a test

i want it in SQL query is it possible to do it without using function or cte


thanks in advance.

What I have tried:

i have tried case when.
and also tried SUBSTRING function of sql.
Posted
Updated 23-Jul-17 4:16am
Comments
PIEBALDconsult 23-Jul-17 9:58am
   
Avoid string manipulation in SQL. If you can, write a CLR function to do it.

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' + '~' -- you must ensure the string ends with the row delimiter
DECLARE @ROWDEL AS NCHAR(1) = '~' -- row delimiter
DECLARE @COLDEL AS NCHAR(1) = ':' -- column delimiter
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
   
Google Search has lots of answers for you: How to split string in SQL[^]
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900