How to convert a string into table

• iex_kris

  Grasshopper

  Points: 15

  More actions

  May 24, 2010 at 4:30 am

  #386993

  How can I convert a string into a table? within the designer of srss 2005.

  The value of the string is: "; 40.7; 38.6; 36.8; 34.0; etc"

  I would like to put it into different rows of the table. So each number in another row.

  To make this some more clear, I made a picture: http://img412.imageshack.us/img412/7565/problemte.jpg

  I tried to use this code:

  Public Function AddCarriageReturns(ByVal s As String) As String

  Dim aryTextFile() As String

  Dim ReturnString As String

  aryTextFile = s.Split(";")

  return String.Join(vbCrLf, aryTextFile)

  End FunctionBut it didnt return the data into different rows.

  I hope someone can help me with this. Thanks

  Kris

• Lowell

  SSC Guru

  Points: 323547

  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!