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

SPLIT Function Expand / Collapse
Author
Message
Posted Saturday, October 13, 2007 6:45 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, February 20, 2014 7:03 AM
Points: 131, Visits: 94
Comments posted to this topic are about the item SPLIT Function


Post #410470
Posted Thursday, December 27, 2007 2:20 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, October 1, 2014 8:20 AM
Points: 932, Visits: 368
Umm, you might want to change your description. The COALESCE function built into SQL Server does not create a concatenated string out of a list of values. From BOL:

COALESCE
Returns the first nonnull expression among its arguments.

If you're referring to a COALESCE UDF, you might want to try to point the user to that function as well.

Just FYI - presumably, I'm the only one of the hundreds of people who read this who's anal-retentive enough to bring it up....



R David Francis
Post #436839
Posted Thursday, December 27, 2007 4:53 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Yesterday @ 1:53 PM
Points: 35,366, Visits: 31,905
Heh... my concern is that UDF's of this nature qualify as RBAR... you get to split exactly one row. That's fine for a single parameter passed in from a GUI, but if you want to split a whole column in a table, you can use a Tally table to do the whole table at once. For example...

--===== Create a test table and some CSV data
-- This is NOT part of the solution... just building test data here
CREATE TABLE #CsvTest (SomeID INT PRIMARY KEY, SomeCsv VARCHAR(200))
INSERT INTO #CsvTest
(SomeID,SomeCsv)
SELECT 1,'Part01,Part02,Part03,Part04,Part05,Part06,Part07,Part08,Part09,Part10' UNION ALL
SELECT 2,'Part01,,Part03,Part04,Part05,Part06,Part07,,Part09,Part10' UNION ALL
SELECT 3,'Part01,Part02,Part03,,Part05,Part06,Part07,Part08,Part09,Part10' UNION ALL
SELECT 4,'Part01,Part02,Part03,Part04,Part05,,Part07,Part08,Part09,Part10' UNION ALL
SELECT 5,'Part01' UNION ALL
SELECT 6,'Part01,Part02' UNION ALL
SELECT 7,',Part02,Part03,Part04,Part05,Part06,Part07,Part08,Part09,' UNION ALL
SELECT 8,NULL UNION ALL
SELECT 9,'' UNION ALL
SELECT 10,','

--===== Split the whole CSV column using a single select with a cross join to the Tally table.
SELECT SomeID,
ElementNum = t.N-LEN(REPLACE(LEFT(','+ct.SomeCsv+',',t.N), ',', '')),
Value = NULLIF(SUBSTRING(','+ct.SomeCsv+',',t.N+1,CHARINDEX(',',','+ct.SomeCsv+',',t.N+1)-t.N-1),' ')
FROM #CsvTest ct
CROSS JOIN dbo.Tally t
WHERE t.N < LEN(','+ct.SomeCsv+',')
AND SUBSTRING(','+ct.SomeCsv+',',N,1) = ','
ORDER BY SomeID,ElementNum

DROP TABLE #CsvTest

...and, just in case you don't know how to make the wonderful little helper table known as a Tally table (it's got LOTS of uses), here's how...
--===== Create and populate the Tally table on the fly
SELECT TOP 11000 --equates to more than 30 years of dates
IDENTITY(INT,1,1) AS N
INTO dbo.Tally
FROM Master.dbo.SysColumns sc1,
Master.dbo.SysColumns sc2

--===== Add a Primary Key to maximize performance
ALTER TABLE dbo.Tally
ADD CONSTRAINT PK_Tally_N
PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100

--===== Allow the general public to use it
GRANT SELECT ON dbo.Tally TO PUBLIC

Of course, we all know it's a mortal database sin to store delimited data in a column... right? ;)


--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 #436859
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse