How to split the Multiple strings values in sqlserver 2008 ?

Posted by Nandkishorre under Sql Server on 5/3/2013 | Points: 10 | Views : 3422 | Status : [Member] | Replies : 2

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

Hi,
here i need spliting 2 strings in below process. How can i split these two strings in sql server.
could any one know, send me the reply for this....

STRING 'A,B,C,D'
STRING '1,2,3,4'

APLHA NUMB
A 1
B 2
C 3
D 4

Regards
Nanda Kishore.CH

Reply | Reply with Attachment

Alert Moderator

Responses

Posted by: aswinialuri-19361 on: 5/3/2013 [Member] Starter | Points: 25

0	Hi You mean you have to split one column data into multiple columns create table splitS(FullName NVARCHAR(50)) select LEFT(fullname, CHARINDEX(' ', fullname + ' ') -1), STUFF(fullname, 1, Len(FullName) +1- CHARINDEX(' ',Reverse(fullname)), '') from splitS insert into splitS values('ASWINI ALURI') I think it will help you Thanks&Regards Mark as Answer if it helps you Thanks&Regards Aswini Aluri Nandkishorre, if this helps please login to Mark As Answer. \| Alert Moderator

Hi

You mean you have to split one column data into multiple columns

create table splitS(FullName NVARCHAR(50))
select LEFT(fullname, CHARINDEX(' ', fullname + ' ') -1),
STUFF(fullname, 1, Len(FullName) +1- CHARINDEX(' ',Reverse(fullname)), '')
from splitS

insert into splitS values('ASWINI ALURI')
I think it will help you
Thanks&Regards

Mark as Answer if it helps you
Thanks&Regards
Aswini Aluri

Nandkishorre, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Niladri.Biswas on: 5/6/2013 [Member] Platinum | Points: 25

0	Here you go Declare @str1 varchar(20) = 'A,B,C,D' Declare @str2 varchar(20) = '1,2,3,4' Declare @delimiter char(1) = ',' ;with cte1 as( SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY(SELECT 1)) ,APLHA = Split.a.value('.', 'VARCHAR(100)') FROM ( SELECT CAST('<X>' + REPLACE(@str1, @delimiter , '</X><X>') + '</X>' AS XML) AS Splitdata ) X CROSS APPLY Splitdata.nodes('/X') Split(a)), cte2 as( SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY(SELECT 1)) ,NUMB = Split.a.value('.', 'VARCHAR(100)') FROM ( SELECT CAST('<X>' + REPLACE(@str2, @delimiter , '</X><X>') + '</X>' AS XML) AS Splitdata ) X CROSS APPLY Splitdata.nodes('/X') Split(a)) select c1.APLHA,c2.NUMB from cte1 c1 join cte2 c2 on c1.ItemNumber = c2.ItemNumber You can use a function to avoid writing the code twice [ left for you as an exercise (: ] You can also look into: http://www.dotnetfunda.com/codes/code1797-split-function-in-sql-server-using-set-base-approach.aspx Best Regards, Niladri Biswas Nandkishorre, if this helps please login to Mark As Answer. \| Alert Moderator

Here you go

Declare @str1 varchar(20) = 'A,B,C,D' 

Declare @str2 varchar(20) = '1,2,3,4' 

Declare @delimiter char(1) = ','



;with cte1 as(

SELECT

			ItemNumber  = ROW_NUMBER() OVER(ORDER BY(SELECT 1))

			,APLHA = Split.a.value('.', 'VARCHAR(100)')

	FROM

		(

			SELECT    

				CAST('<X>' + REPLACE(@str1,  @delimiter , '</X><X>') + '</X>' AS XML) AS Splitdata  

		) X  

	CROSS APPLY Splitdata.nodes('/X') Split(a)),

cte2 as(

SELECT

			ItemNumber  = ROW_NUMBER() OVER(ORDER BY(SELECT 1))

			,NUMB = Split.a.value('.', 'VARCHAR(100)')

	FROM

		(

			SELECT    

				CAST('<X>' + REPLACE(@str2,  @delimiter , '</X><X>') + '</X>' AS XML) AS Splitdata  

		) X  

	CROSS APPLY Splitdata.nodes('/X') Split(a))



select c1.APLHA,c2.NUMB

from cte1 c1

join cte2 c2 on c1.ItemNumber = c2.ItemNumber

You can use a function to avoid writing the code twice [ left for you as an exercise (: ]

You can also look into: http://www.dotnetfunda.com/codes/code1797-split-function-in-sql-server-using-set-base-approach.aspx

Best Regards,
Niladri Biswas

Nandkishorre, if this helps please login to Mark As Answer. | Alert Moderator

Latest Posts