SPLIT Function

• Bhushan

  Default port

  Points: 1427

  More actions

  October 13, 2007 at 6:45 pm

  #375821

  Comments posted to this topic are about the item SPLIT Function

• RD Francis

  SSCarpal Tunnel

  Points: 4867

  More actions

  December 27, 2007 at 2:20 pm

  #763094

  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

• Jeff Moden

  SSC Guru

  Points: 1003853

  More actions

  December 27, 2007 at 4:53 pm

  #763107

  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.

  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)