Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Lokeshkumar » SQL Function to Split Comma Separated Values...
Add to briefcase
19 posts, Page 1 of 2
12»»

SQL Function to Split Comma Separated Values and Insert into Table Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
Lokeshkumar
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: Tuesday, September 18, 2012 1:07 AM
Points: 88, Visits: 39
Comments posted to this topic are about the item SQL Function to Split Comma Separated Values and Insert into Table
Post #1027765
Gourav S
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: Tuesday, April 09, 2013 9:17 AM
Points: 3, Visits: 19
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
FortyEightK
Posted Tuesday, November 30, 2010 2:02 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, May 13, 2013 3:18 AM
Points: 14, Visits: 248
Been using a variation of this function for a while. *VERY* useful!
Post #1027799
laura_b
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
Lokeshkumar
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: Tuesday, September 18, 2012 1:07 AM
Points: 88, Visits: 39
Yes we can make that function as generic by adding the another parameter as you said.
Post #1027881
Jeff Moden
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 @ 6:23 AM
Points: 32,903, Visits: 26,784
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."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #1027925
James Goodwin
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: Wednesday, April 10, 2013 6:10 AM
Points: 1,322, Visits: 1,070
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
WayneS
Posted Tuesday, November 30, 2010 7:37 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 2:02 PM
Points: 6,367, Visits: 8,228
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
SQLRNNR
Posted Tuesday, November 30, 2010 8:43 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 1:07 PM
Points: 18,733, Visits: 12,332
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 2008

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
GSquared
Posted Tuesday, November 30, 2010 8:55 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Monday, May 06, 2013 1:09 PM
Points: 15,439, Visits: 9,569
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
19 posts, Page 1 of 2
12»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.