Converting Row into columns using Field Seperator

• Divya Prakash

  Grasshopper

  Points: 23

  More actions

  October 14, 2003 at 11:50 pm

  #358489

  Hi,

  I have a row which has Data format

  Data1|Data2|Data3|Data4|Data5|Data6

  I need to split this row into columns such that each Data1, Data2 should appear in a seperate column.

  Pls help.

  Regards,

  Prakash

• rs

  SSCrazy

  Points: 2819

  More actions

  October 17, 2003 at 8:55 am

  #478650

  Prakash,

  Use the following function to split the row

  CREATE FUNCTION dbo.fn_Split(@String VARCHAR(8000), @Delimiter CHAR(1))

  RETURNS @Results TABLE (Items VARCHAR(8000))

  AS

  BEGIN

  DECLARE @index INT

  DECLARE @SLICE NVARCHAR(4000)

  SET @index = 1

  WHILE @index !=0

  BEGIN

  --FIND OUT THE FIRST OCCURENCE OF THE DELIMITER

  SET @index = CHARINDEX(@Delimiter,@STRING)

  --GET THE FISRT PART OF THE STRING

  IF @index !=0

  SELECT @SLICE = LEFT(@STRING,@INDEX - 1)

  ELSE

  SELECT @SLICE = @STRING

  -- PUT THE ITEM INTO THE RESULTS SET

  INSERT INTO @Results(Items) VALUES(@SLICE)

  -- CHOP THE ITEM REMOVED OFF THE MAIN STRING

  SET @STRING = RIGHT(@STRING,LEN(@STRING) - @index)

  -- BREAK OUT

  IF LEN(@STRING) = 0 BREAK

  END

  RETURN

  END

  usage

  SELECT * FROM dbo.fn_Split('Data1|Data2|Data3|Data4|Data5|Data6','|')

  Result Set

  Data1

  Data2

  Data3

  Data4

  Data5

  Data6

  If you have any questions please let me know.

  Thanks,

  Sreeni.