Click here to monitor SSC

Split csv entries from a column in a table

Published 13 December 2013 12:26 pm

This is all in a single query without having to create a function. The separator is in a cte so doesn’t need to be declared separately. For longer separators include the length in this cte and use it for the length adjustments in the query.

create table #a (rowid int, csvstring varchar(100))
insert #a select 1, 'a,b,c,asd,yyyy'
insert #a select 2, 'x'
insert #a select 3, 'fhasgdff,wretyr'
;with csv as (select s=',')
, cte1 as
(
select rowid=rowid, csvstring = csvstring + csv.s, strtloc = 1, endloc = charindex(csv.s,csvstring+csv.s)-1 from #a,csv where len(csvstring) > 0
union all
select rowid, csvstring, strtloc = endloc+2, endloc = charindex(csv.s,csvstring,endloc+2)-1 from cte1,csv where charindex(csv.s,csvstring,endloc+2) > 0
)
, cte as
(
select rowid, str=substring(csvstring,strtloc, endloc-strtloc+1), strtloc
from cte1
)
select rowid, str
from cte
order by rowid, strtloc

5 Responses to “Split csv entries from a column in a table”

  1. Laren Hagen says:

    Assuming you have a Number/Tally table in your DB, it would be possible to solve the same challenge like so:

    DECLARE @SourceString VARCHAR(100)
    SET @SourceString = ‘test one two three four’

    SELECT SUBSTRING(@SourceString, N, CHARINDEX(‘ ‘, @SourceString + ‘ ‘, N) – N) AS Word
    , N AS Position
    FROM util.Number
    WHERE N < LEN(@SourceString)
    AND SUBSTRING(' ' + @SourceString, N, 1) = ' '

    Put that into a Function (optionally accepting a different separator character) and voila: you're done!

    Has an added benefit of avoiding the recursion inherent in the above method (more important as the string length grows).

    Hope this helps,

  2. Phil Factor says:

    @Laren,
    Your code doesn’t work for single-character strings.
    Also you don’t explain what is behind the ‘Voila, it just works’.

    create table #a (rowid int, csvstring varchar(100))
    insert #a select 1, ‘a,b,c,asd,yyyy’
    insert #a select 2, ‘x’
    insert #a select 3, ‘fhasgdff,wretyr’

    IF OBJECT_ID (N’dbo.SplitString’) IS NOT NULL
    DROP FUNCTION dbo.SplitString
    GO

    CREATE FUNCTION dbo.SplitString(@CSVstring Varchar(100))
    RETURNS TABLE
    AS RETURN
    (
    SELECT SUBSTRING( @CSVString, Number, CHARINDEX(‘,’, @CSVString + ‘,’, Number) – Number) AS Word
    , Number AS Position
    FROM Numbers
    WHERE Number <= LEN( @CSVString)
    AND SUBSTRING(‘,’ + @CSVString, Number, 1) = ‘,’
    )
    GO
    SELECT RowID, word,position from #a cross apply dbo.SplitString(csvString)

  3. Laren Hagen says:

    @Phil
    I’m not sure what you’re seeing re: single-character strings. To clarify: are you saying that it doesn’t work if the variable being split is declared as a single character then nothing will be returned?

    I see that issue, but it appears that even a single character will be treated properly if the variable itself is set larger (at least from what I’ve seen), which should be covered by its encapsulation in a function.

    Thanks very much for the complete example! Good point.

  4. Phil Factor says:

    @Laren
    It failed with ‘x’ (the second INSERT in Nigel’s test, due to a …
    WHERE N < LEN(@SourceString)
    … which I reckon should have been…
    WHERE N <= LEN(@SourceString)

  5. Phil Factor says:

    @Nigel

    I reckon that this is the fastest and easiest way of doing this, just as long as you don’t need the strtloc value in the result

    create table #a (rowid int, csvstring varchar(100))

    insert #a select 1, 'a,b,c,asd,yyyy'

    insert #a select 2, 'x'

    insert #a select 3, 'fhasgdff,wretyr'

     

        SELECT  Rowid,LTRIM(x.y.value('.','Varchar(100)')) AS [Tag]

        FROM

        (SELECT rowid,

             CONVERT(XML,'<list><i>' + REPLACE(csvString,',','</i><i>')+ '</i></list>')

             AS XMLList

             FROM   #a)g

        CROSS APPLY XMLList.nodes('/list/i/text()')AS x ( y)

Leave a Reply