-- 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

This page was last updated on May 01, 2006 04:28 PM.