How to convert a string into table

• Lowell

  SSC Guru

  Points: 323741

  More actions

  May 24, 2010 at 9:44 am

  #1171641

  Kris there is a difference between a string which happens to have Carriage Returns, and splitting a string into rows;

  search the script contributions for "Split", for a huge collection of different ways tosplit a string to rows;

  here is just one example:

  create function [dbo].[fn_split](

  @str varchar(8000),

  @spliter char(1)

  )

  returns @returnTable table (idx int primary key identity, item varchar(8000))

  as

  begin

  declare @spliterIndex int

  select @str = @str + @spliter

  SELECT @str = @spliter + @str + @spliter

  INSERT @returnTable

  SELECT SUBSTRING(@str,N+1,CHARINDEX(@spliter,@str,N+1)-N-1)

  FROM dbo.Tally

  WHERE N < LEN(@str)

  AND SUBSTRING(@str,N,1) = @spliter

  ORDER BY N

  return

  end

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!