Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

SQL Function to Split Comma Separated Values and Insert into Table Expand / Collapse
Author
Message
Posted Monday, November 29, 2010 11:44 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Saturday, August 31, 2013 4:28 AM
Points: 88, Visits: 43
Comments posted to this topic are about the item SQL Function to Split Comma Separated Values and Insert into Table
Post #1027765
Posted Tuesday, November 30, 2010 12:04 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, August 25, 2014 3:19 AM
Points: 3, Visits: 22
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.
Post #1027772
Posted Tuesday, November 30, 2010 2:02 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Today @ 7:55 AM
Points: 31, Visits: 326
Been using a variation of this function for a while. *VERY* useful!
Post #1027799
Posted Tuesday, November 30, 2010 2:36 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, April 19, 2013 2:00 AM
Points: 1, Visits: 82
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

Post #1027815
Posted Tuesday, November 30, 2010 5:02 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Saturday, August 31, 2013 4:28 AM
Points: 88, Visits: 43
Yes we can make that function as generic by adding the another parameter as you said.
Post #1027881
Posted Tuesday, November 30, 2010 6:31 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 10:11 AM
Points: 36,994, Visits: 31,513
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1027925
Posted Tuesday, November 30, 2010 7:20 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, January 27, 2014 10:14 AM
Points: 1,322, Visits: 1,091
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) = ','

Post #1027973
Posted Tuesday, November 30, 2010 7:37 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:50 AM
Points: 6,594, Visits: 8,879
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
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, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #1027984
Posted Tuesday, November 30, 2010 8:43 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 9:36 AM
Points: 21,644, Visits: 15,314
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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1028027
Posted Tuesday, November 30, 2010 8:55 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
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
Post #1028034
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse