Extract values from a string using a delimiter

• Tim Sneller

  Grasshopper

  Points: 13

  More actions

  September 23, 2007 at 2:26 am

  #198045

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

• RyanRandall

  SSChampion

  Points: 13623

  More actions

  November 1, 2007 at 6:07 am

  #747390

  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-2 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-2 = 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-2 -->B,C[/font]

  Ryan Randall

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