14,969,978 members
1.00/5 (1 vote)
See more:
I am having a string with comma separated data and I want to write a query that converts this data in form of rows and columns.
Posted
Sanjay K. Gupta 7-Nov-12 2:18am

Need more explanation. Please put your data, so that we may give you suggestions

## Solution 1

Use it as a function Hope it will help u.If helped Vote it.

SQL
```CREATE FUNCTION DBO.FUN_SPLIT
(
@DATA VARCHAR(2000),
@SEP VARCHAR(5)
)
RETURNS @TEMP TABLE (ID INT IDENTITY(1,1), DATA NVARCHAR(100))
AS
/****************************************************************************************  TESTING	SELECT * FROM DBO.FUN_SPLIT('A,B,C',',')
**************************************************************************************/
BEGIN
DECLARE @CNT INT
SET @CNT = 1
WHILE (CHARINDEX(@SEP,@DATA)>0)
BEGIN
INSERT INTO @TEMP (DATA)
SELECT	DATA = LTRIM(RTRIM(SUBSTRING(@DATA,1,CHARINDEX(@SEP,@DATA)-1)))
SET @DATA = SUBSTRING(@DATA,CHARINDEX(@SEP,@DATA)+1,LEN(@DATA))
SET @CNT = @CNT + 1
END
INSERT INTO @TEMP (DATA)
SELECT DATA = LTRIM(RTRIM(@DATA))
RETURN
END```
kankeyan 7-Nov-12 2:23am

We have written a function for this case in our project. Hope this will help.

## Solution 2

Try:

SQL
```CREATE FUNCTION [dbo].[String_Tokenizer]
(
@RowData nvarchar(max),
@SplitOn nvarchar(5)
)
RETURNS @RtnValue table
(
Id int identity(1,1),
Data nvarchar(100)
)
AS
BEGIN
Declare @Cnt int
Set @Cnt = 1

While (Charindex(@SplitOn,@RowData)>0)
Begin
Insert Into @RtnValue (data)
Select
Data = ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1)))

Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData))
Set @Cnt = @Cnt + 1
End

Insert Into @RtnValue (data)
Select Data = ltrim(rtrim(@RowData))

Return
END```

Sample code:
SQL
`select * from [dbo].[String_Tokenizer]('a,b,c,d',',')`

Output:
```ID      Data
1	a
2	b
3	c
4	d```
v2

## Solution 3

Try

SQL
```DECLARE @param VARCHAR(MAX) = '3,24,2,45';
DECLARE @Xparam XML;

SELECT @Xparam = CAST('<i>' + REPLACE(@param,',','</i><i>') + '</i>' AS XML)
SELECT x.i.value('.','INT') FROM @Xparam.nodes('//i') x(i)```

Thank you