Need to convert a string into tabular form

Question

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 6-Nov-12 20:15pm

Member 9578806

Add a Solution

Comments

Sanjay K. Gupta 7-Nov-12 2:18am

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

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

kankeyan · Answer 1 · 2012-11-06T20:21:00

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

Shanalal Kasim · Answer 2 · 2012-11-06T21:23:00

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

damodara naidu betha · Answer 3 · 2012-11-06T23:47:00

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

Posted 6-Nov-12 23:47pm

damodara naidu betha

Need to convert a string into tabular form

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0