Convert textlines in records

Question

I am looking for a way, that I can convert lines of a text in separate records. Perhaps there is someone who has an idea?

I have the following record in a table:

1          blabla          Messe\nJahr\nLand

The third field is a textfield. The content is a text with three lines.

now, I should write a select, which gives me as a result three records

1  Memo
2  Jahr
3  Land

This is a good opportunity to normalize the table so that the 3rd field doesn't need to be parsed. Also check out this answer of a similar nature: http://stackoverflow.com/questions/13873701/convert-comma-separated-column-value-to-rows — zedfoxus, Jan 08 '14 at 16:53

score 1 · Accepted Answer · answered Jan 08 '14 at 16:59

i suggest using a cursor and then splitting the string using charpos. as i did not know the name of your table or column, i used table name a, column name a.

declare c cursor for
 select a 
   from a

declare @p varchar(max)

open c
fetch next from c into @p;

while @@FETCH_STATUS = 0
begin
  while CHARINDEX('\n',@p,0) > 0
  begin
    select SUBSTRING(@p,0,charindex('\n',@p,0))
    set @p = SUBSTRING(@p,charindex('\n',@p,0)+2, LEN(@p)-charindex('\n',@p,0)-1);
  end
  select @p;
  fetch next from c into @p;
end

DEALLOCATE c

i tested this using

create table a (a varchar(50))
insert into a values ('a\nb\nc')
insert into a values ('d\ne\nf')

score 0 · Answer 2 · answered Jan 08 '14 at 16:49

use this..

  ALTER FUNCTION [dbo].[GetWordsFromString] 
(
@string nvarchar(max)
)
RETURNS 
@out TABLE 
(
Name nvarchar(200)
)
AS
BEGIN
    DECLARE   @pos        int,
              @nextpos    int,
              @valuelen   int

SELECT @pos = 0, @nextpos = 1

   WHILE @nextpos > 0
   BEGIN
      SELECT @nextpos = charindex('\', @string, @pos + 1)
      SELECT @valuelen = CASE WHEN @nextpos > 0
                              THEN @nextpos
                              ELSE len(@string) + 1
                         END - @pos - 1
      INSERT @out 
         VALUES (convert(nvarchar, substring(@string, @pos + 1, @valuelen)))
      SELECT @pos = @nextpos
   END

    RETURN 
END

Convert textlines in records

2 Answers2