Extract values from a string using a delimiter

  • Tim Sneller

    Grasshopper

    Points: 13

    Comments posted to this topic are about the item Extract values from a string using a delimiter

  • RyanRandall

    SSChampion

    Points: 13623

    Just for fun :D, here's the same sort of thing using the recursive CTE technique...

    [font="Courier New"]DECLARE @String VARCHAR(255)

    DECLARE @Delimiter CHAR(1)

    DECLARE @Occurrence INT

    DECLARE @Num_Fields INT

    SET @String = 'A,B,C,D,E'

    SET @Delimiter = ','

    SET @Occurrence = 2

    SET @Num_Fields = 2

    DECLARE @S VARCHAR(255)

    SET @String = @String + @Delimiter

    ; WITH

    a AS (

    SELECT CAST(NULL AS VARCHAR(255)) AS x, @String y, 0 AS i

    UNION ALL

    SELECT LEFT(y, CHARINDEX(',', y)-1), SUBSTRING(y, CHARINDEX(',', y)+1, 255), i + 1 FROM a WHERE y <> '')

    SELECT @S = ISNULL(@s + ',', '') + x

    FROM a, (SELECT CASE WHEN @Occurrence < 0 THEN COUNT(*) + @Occurrence ELSE @Occurrence END AS o FROM a) b

    WHERE i BETWEEN o AND o + @Num_Fields - 1

    PRINT @S -->B,C[/font]

    Ryan Randall

    Solutions are easy. Understanding the problem, now, that's the hard part.

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply