Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 HELP HELP HELP!!!..

Author  Topic 

tonydang2002
Starting Member

15 Posts

Posted - 2014-03-07 : 02:12:31
Hi,
I've been looking around and see alot of split function but couldn't find anything that help me to split string into multiple rows.

Please help me with below case, Thanks.

I have data string as below: 3 rows with '|' as col delimiter and ',' as row terminator:
1001|Tony|92841|,|1002|Dennis|92683|,1003|David|92541

I'm looking for SQL script or SP that I can run to split into multiple rows and insert this into a SQL table.

something like:
EXEC stringsplit '1001|Tony|92841|,|1002|Dennis|92683|,1003|David|92541'

and my end result would be:
My table:
ID Name Zipcode
1001 Tony 92841
1002 Dennis 92683
1003 David 92541

Thank you for your help!
Tony

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-03-07 : 02:15:15
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=25830&SearchTerms=CSVTable
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76033


KH
[spoiler]Time is always against us[/spoiler]

Go to Top of Page

Muj9
Yak Posting Veteran

75 Posts

Posted - 2014-03-07 : 10:07:53
CREATE FUNCTION ParseValues
(@String varchar(8000), @Delimiter varchar(10) )
RETURNS @RESULTS TABLE (ID int identity(1,1), Val varchar(50))
AS
BEGIN
DECLARE @Value varchar(100)
WHILE @String is not null
BEGIN
SELECT @Value=
CASE WHEN PATINDEX('%'+@Delimiter+'%',@String) >0
THEN LEFT(@String,PATINDEX('%'+@Delimiter+'%',@String)-1) ELSE @String END,
@String=
CASE WHEN PATINDEX('%'+@Delimiter+'%',@String) >0
THEN SUBSTRING(@String,PATINDEX('%'+@Delimiter+'%',@String)+LEN(@Delimiter),LEN(@String))ELSE NULL END
INSERT INTO @RESULTS (Val)
SELECT @Value
END
RETURN
END


SELECT f.Val
FROM yourtablename t
CROSS APPLY dbo.ParseValues(t.yourcolumnname,'|')f
Go to Top of Page

tonydang2002
Starting Member

15 Posts

Posted - 2014-03-07 : 12:14:10
None of these scripts work in my case. what I have is data string, not table. I'm looking for a script/function/SP to feed that data string in and insert it into a table with multiple rows as my end result.

Thank you.
Tony
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-03-08 : 19:51:24
quote:
Originally posted by tonydang2002

None of these scripts work in my case. what I have is data string, not table. I'm looking for a script/function/SP to feed that data string in and insert it into a table with multiple rows as my end result.

Thank you.
Tony


you did not refer to the links that i posted ?


KH
[spoiler]Time is always against us[/spoiler]

Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-03-08 : 19:55:43
[code]
declare @str varchar(100)

select @str = '1001|Tony|92841|,1002|Dennis|92683|,1003|David|92541'

select dbo.fnParseString(-1, '|', Data) as [ID],
dbo.fnParseString(-2, '|', Data) as [Name],
dbo.fnParseString(-3, '|', Data) as [Zipcode]
from dbo.fnParseList(',', @str)
[/code]


KH
[spoiler]Time is always against us[/spoiler]

Go to Top of Page
   

- Advertisement -