Click here to Skip to main content
14,969,978 members
Please Sign up or sign in to vote.
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
Comments
Sanjay K. Gupta 7-Nov-12 2:18am
   
Need more explanation. Please put your data, so that we may give you suggestions

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
   
Comments
kankeyan 7-Nov-12 2:23am
   
We have written a function for this case in our project. Hope this will help.
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
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
   

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