SQL - how to turn a delimited string into rows of a table

tommym121
tommym121 used Ask the Experts™
on
I have a string like 'item 1,item 2,item 3,item 4' into rows of a table (say MyTable) so when
I

Select * From MyTable

I will get

Item 1
Item 2
Item 3
Item 4
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Author

Commented:
Seventhan,

I read the article, but still not sure how to use pivot or unpivot to achieve the result.

Would you be able to show me with an example.  Thanks.
Top Expert 2011
Commented:
You can create a function adn use it like this:

select * from dbo.fnTextToTable(@data)
where @data is your string

The function is like this:


CREATE FUNCTION [dbo].[fnTextToTable]
  (@Data TEXT)
  RETURNS @Tbl TABLE (ID VARCHAR(8000))
AS
 
  BEGIN
  DECLARE @Pstn BIGINT, @Value VARCHAR(8000),@NewText VARCHAR(8000)
  SELECT @NewText='/',@Pstn=0
 
  WHILE RTRIM(@NewText) <> ''
    BEGIN
    SET @NewText = SUBSTRING(@Data, @Pstn + 1, 7900)
   
    IF DATALENGTH(@NewText) = 7900
      SET @NewText = SUBSTRING(@NewText,1,CHARINDEX(',', @NewText + ',', DATALENGTH(@NewText) - 1)) + ','
    ELSE
      SET @NewText = @NewText + ','
 
    SET @Pstn = @Pstn + DATALENGTH(@NewText)
    WHILE RTRIM(@NewText) <> ''
      BEGIN
      SET @Value = SUBSTRING(@NewText, 1, CHARINDEX(',', @NewText, 1) -1)
      SET @NewText = STUFF(@NewText, 1, DATALENGTH(@Value) + 1, '')
      IF RTRIM(@Value) <> '' INSERT INTO @Tbl(ID)VALUES(@Value)
      END
    END
 
  RETURN
END

Author

Commented:
thanks.  Got it.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial