SQL Function to Split Comma Separated Values and Insert into Table

Question

Post reply

SQL Function to Split Comma Separated Values and Insert into Table

Lokeshkumar

SSC-Addicted

Points: 476
More actions
November 29, 2010 at 11:44 pm

#232054

Comments posted to this topic are about the item SQL Function to Split Comma Separated Values and Insert into Table

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply

Gourav S SSC Enthusiast Points: 155 More actions · Answer 1

You can make this function more generic by taking one more parameter

@Separator VARCHAR(5)

and inside the code instead of hard-coded comma you could use this parameter.

This will handle the cases where individual values in @InputString contain commas themselves, where you would want to use a different separator than comma.

FortyEightK Mr or Mrs. 500 Points: 553 More actions · Answer 2

Been using a variation of this function for a while. *VERY* useful!

laura_b SSC Journeyman Points: 85 More actions · Answer 3

Can be used also:

ALTER FUNCTION [dbo].[fx_Split]

(

@delimited nvarchar(max),

@delimiter nvarchar(100)

) RETURNS @t TABLE

(

val nvarchar(max)

)

AS

BEGIN

declare @xml xml

set @xml = N'<root><r>' + replace(@delimited,@delimiter,'</r><r>') + '</r></root>'

insert into @t(val)

select LTRIM(RTRIM(r.value('.','varchar(30)'))) as item

from @xml.nodes('//root/r') as records(r)

RETURN

END

Lokeshkumar SSC-Addicted Points: 476 More actions · Answer 4

Yes we can make that function as generic by adding the another parameter as you said.

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 5

Oh... be careful folks. There are a whole lot of high performance methods for splitting delimited data instead of using the RBAR of a While Loop. Search for them. Be careful to test XML methods, as well. Some are very, very good. Some are very, very bad.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

James Goodwin Hall of Fame Points: 3691 More actions · Answer 6

I really don't like the WHILE loop in there.

I use this code, (which may or may not be from Jeff Moden) which requires a table of numbers from 1 to 8000:

ALTER FUNCTION dbo.TableFromList(@List varchar(8000))

RETURNS TABLE

AS RETURN

SELECT LTRIM(RTRIM(SUBSTRING(ListID, number+1, CHARINDEX(',', ListID, number+1)-number - 1))) AS Value

FROM (SELECT ',' + @List + ',' AS ListID) AS InnerQuery

JOIN Numbers n

ON n.Number < LEN(InnerQuery.ListID)

WHERE SUBSTRING(ListID, number, 1) = ','

WayneS SSC Guru Points: 95409 More actions · Answer 7

The latest (and fastest) Delimited String Splitter Function can be found here. It avoids all the RBAR of the while loop. It does require a tally or numbers table, but it's wicked fast when you run it against a table of one million rows that has a column that needs to be split. It works with any delimiter, and columns up to varchar(7999).

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

SQLRNNR SSC Guru Points: 281334 More actions · Answer 8

Others have already thrown out some concerns. I will echo the same sentiments. Thanks Wayne for providing a link to some high performing code.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

GSquared SSC Guru Points: 260824 More actions · Answer 9

Just to add another vote for NOT using this. There are MUCH better methods than stepping through with a string function in a loop.

Also, in current versions of SQL Server, you shouldn't be passing a delimited list anyway. You should use a table parameter or an XML parameter. Either one is better than a delimited list, with table being the top choice.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Steven Willis SSCrazy Eights Points: 9893 More actions · Answer 10

I've been using Jeff Moden's procedures and a tally table for years. The function in the OP is not the correct way to split CSVs, with the possible exception of a quick-and-dirty one time application for someone in a hurry who has never used CTEs before. But it's not that hard and if you've had to do it once, it's likely you'll have to do it again sometime.

FortyEightK Mr or Mrs. 500 Points: 553 More actions · Answer 11

Just switched to WayneS' non-RBAR function. Now that's FAST!

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 12

WayneS (11/30/2010)
The latest (and fastest) Delimited String Splitter Function can be found here.

Thanks for the link!

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

WayneS SSC Guru Points: 95409 More actions · Answer 13

chris-860960 (11/30/2010)
Just switched to WayneS' non-RBAR function. Now that's FAST!

It's not "my" function - it comes from Jeff Moden. It's just what is in use around here, with little tweaks here and there to make it as fast as it possible can be.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

TurnerC SSC Veteran Points: 280 More actions · Answer 14

Hello,

Would you happen to have any suggestions on how to do the opposite, take a result set and create one line csv file. Example 20 lines of data are returned in a result set displaying the total # of Available hospital beds. How can I get the 20 lines of data on one csv line. any help or assistance would be greatly appreciated.