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.
| 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|92541I'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 Zipcode1001 Tony 928411002 Dennis 926831003 David 92541Thank you for your help!Tony |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
|
|
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))ASBEGINDECLARE @Value varchar(100)WHILE @String is not nullBEGIN 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 ENDINSERT INTO @RESULTS (Val)SELECT @ValueENDRETURNENDSELECT f.ValFROM yourtablename tCROSS APPLY dbo.ParseValues(t.yourcolumnname,'|')f |
 |
|
|
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 |
|
|
|
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] |
|
|
|
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] |
|
|
|
|
|
|
|
|
HELP HELP HELP!!!..