How to split the Multiple strings values in sqlserver 2008 ?

Posted by Nandkishorre under Sql Server on 5/3/2013 | Points: 10 | Views : 2302 | Status : [Member] | Replies : 2
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




Responses

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

Up
0
Down
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

Up
0
Down
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

Login to post response