Splitting a CSV demilited Column to rows using DelimitedSplit8K function

• s.rey

  SSC Veteran

  Points: 219

  More actions

  July 27, 2011 at 9:39 am

  #390063

  Hello,

  I am trying to create a mapper table out of the existing table which has a CSV delimited column.

  I am trying to use DelimitedSplit8K iTVF. I am running this on SQL 2008 and getting the following error.

  Here is the function definition for DelimitedSplit8K:

  SET ANSI_NULLS ON

  GO

  SET QUOTED_IDENTIFIER ON

  GO

  ALTER FUNCTION dbo.DelimitedSplit8K

  --===== Define I/O parameters

  (@pString VARCHAR(8000), @pDelimiter CHAR(1))

  RETURNS TABLE WITH SCHEMABINDING AS

  RETURN

  --===== "Inline" CTE Driven "Tally Table" produces values from 0 up to 10,000...

  -- enough to cover VARCHAR(8000)

  WITH E1(N) AS (

  SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL

  SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL

  SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1

  ), --10E+1 or 10 rows

  E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows

  E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max

  cteTally(N) AS (--==== This provides the "zero base" and limits the number of rows right up front

  -- for both a performance gain and prevention of accidental "overruns"

  SELECT 0 UNION ALL

  SELECT TOP (DATALENGTH(ISNULL(@pString,1))) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4

  ),

  cteStart(N1) AS (--==== This returns N+1 (starting position of each "element" just once for each delimiter)

  SELECT t.N+1

  FROM cteTally t

  WHERE (SUBSTRING(@pString,t.N,1) = @pDelimiter OR t.N = 0)

  )

  --===== Do the actual split. The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found.

  SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY s.N1),

  Item = SUBSTRING(@pString,s.N1,ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,s.N1),0)-s.N1,8000))

  FROM cteStart s

  ;

  Here is my test case:

  CREATE TABLE #List (List varchar(100))

  INSERT INTO #List VALUES ('AB,CD,EF,GH,IJ,KL,MN,OP,QR,ST,UV,WZ,YZ')

  Table has been created and populated with data

  select * from #List

  List

  -----------------------------------------

  AB,CD,EF,GH,IJ,KL,MN,OP,QR,ST,UV,WZ,YZ

  Verified that the data is selected

  select f.*

  FROM #List l

  CROSS APPLY DelimitedSplit8K(l.List, ',') f

  Msg 102, Level 15, State 1, Line 3

  Incorrect syntax near '.'.

  I get the above error message..... I tried with different test data...It wouldn't work....

  See below:

  select f.*

  FROM #List l

  CROSS APPLY DelimitedSplit8K('AB,CD,EF,GH,IJ,KL,MN,OP,QR,ST,UV,WZ,YZ', ',') f

  Above query return the result fine...

  1AB

  2CD

  3EF

  4GH

  5IJ

  6KL

  7MN

  8OP

  9QR

  10ST

  11UV

  12WZ

  13YZ

  not sure what is going wrong when I pass the CSV Delimited column name.

  Please advise

• Robert Klimes

  SSCoach

  Points: 17865

  More actions

  July 27, 2011 at 9:57 am

  #1359431

  Check the compatibility level of the database. It worked fine for me until i changed the compatibility level of the database that the function was installed in to sql2000.

  Bob
  -----------------------------------------------------------------------------
  How to post to get the best help[/url]

• SQLRNNR

  SSC Guru

  Points: 281344

  More actions

  July 27, 2011 at 9:59 am

  #1359434

  Same experience - works fine for me. I would check compat level and service packs.

  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