Binaryworld - Stripping individual values from a comma-separated string using SELECT ... [ SQL Server -> T-SQL ]

SELECT statement that demonstrate how to strip individual values from a comma-separated string. This uses a table of Numbers to solve the procedural problem.

Click here to copy the following block

-- For example, use a simple string in a variable. This method
-- can be easily adopted to look at values in a column of a table.
DECLARE @csv varchar( 255 )
SET @csv = ',value1,value2,value3,value4,value5,'
-- Use the table of numbers to loop through the string &
-- get the individual values out. The logic is to look for
-- the positions of the comma delimiting the value.

-- SQL70/2000 specific using the CHARINDEX function
SELECT SUBSTRING( @csv , n.Number + 1 ,
         -- Get position of next comma using the CHARINDEX with
         -- search from position parameter
         CHARINDEX( ',' , @csv , n.Number + 1 ) - n.Number - 1 ) AS Value
FROM Numbers AS n
WHERE n.Number BETWEEN 1 And LEN( @csv ) - 1 And
    -- Find the position of commas only except the last one
   SUBSTRING( @csv , n.Number , 1 ) = ','

-- Just for demonstration, this shows how to get the position of the values
SELECT Value ,
    -- Count Occurrence of comma to get position
    LEN( DelimValue ) - LEN( REPLACE( DelimValue , ',' , '' ) ) - 1 AS Position
FROM (
    SELECT SUBSTRING( @csv , n.Number + 1 ,
             -- Get position of next comma using the CHARINDEX with
             -- search from position parameter
             CHARINDEX( ',' , @csv , n.Number + 1 ) - n.Number - 1 ) ,
        -- Get the string till the current comma
        SUBSTRING( @csv , 1 ,
             CHARINDEX( ',' , @csv , n.Number + 1 ) )
     FROM Numbers AS n
    WHERE n.Number BETWEEN 1 And LEN( @csv ) - 1 And
        -- Find the position of commas only except the last one
       SUBSTRING( @csv , n.Number , 1 ) = ','
) AS n( Value , DelimValue )

-- The methods below are SQL6x specific queries and can be ported to other databases.
-- Evaluate cost of both methods & use it.

-- Method #1: Using standard JOIN & GROUP BY clause ( SQL6x )
SELECT SUBSTRING( @csv , StartPos + 1 , EndPos - StartPos - 1 ) AS Value
FROM (
    SELECT n1.Number AS StartPos , MIN( n2.Number ) as EndPos
     FROM Numbers n1
     JOIN Numbers n2
      ON n2.Number > n1.Number
    WHERE n1.Number BETWEEN 1 And LEN( @csv ) - 1 And
       n2.Number BETWEEN 1 And LEN( @csv ) And
       SUBSTRING( @csv , n1.Number , 1 ) = ',' And
       SUBSTRING( @csv , n2.Number , 1 ) = ','
    GROUP BY n1.Number
) AS n

-- Method #2: Using a correlated query ( SQL6x )
SELECT SUBSTRING( @csv , StartPos + 1 , EndPos - StartPos - 1 ) as Value
FROM (
    SELECT n1.Number AS StartPos ,
        ( SELECT MIN( n2.Number )
         FROM Numbers n2
         WHERE n2.Number BETWEEN 1 And LEN( @csv ) And
            SUBSTRING( @csv , n2.Number , 1 ) = ',' And
            n2.Number > n1.Number
        ) AS EndPos
     FROM Numbers n1
    WHERE n1.Number BETWEEN 1 And LEN( @csv ) - 1 And
       SUBSTRING( @csv , n1.Number , 1 ) = ','
) AS n