Extract memo line with SQL

[former_member1315653]

I need to read the third line from memo fields in a SELECT statement but have no idea how to do that.

Can anyone help please?

[joachim_drr]

I would put it into a function:

create function extract_line(s string, d string, no integer)
returns string
begin
  declare @t string;
  declare @pos integer;
  @t=coalesce(s,'');
  while no>1 do
    @pos=position(d in @t);
     if @pos>0 then
      @t=substring(@t,@pos+length(d),length(@t));
     else @t='';
     end;
     no=no-1;
  end;
  @pos=position(d in @t);
  if @pos>0 then
    @t=substring(@t,1,@pos-1);
  end;
  return @t;
end;

usage:

declare @st string;
declare @del string;

@del=CHAR(13)+CHAR(10);
@st='abc'+@del+'def'+@del+'ghi'+@del+'jkl';
select 
  extract_line(@st,@del,1),
  extract_line(@st,@del,2),
  extract_line(@st,@del,3),
  extract_line(@st,@del,4),
  extract_line(@st,@del,5),
  extract_line(@st,@del,6)
from system.iota;

or, for a memo field

select 
  id, extract_line(memo_field,CHAR(13)+CHAR(10),3)
from system.iota;

[Former Member]

You can use the code I have posted here:

http://stackoverflow.com/questions/5352547/splitting-a-string-into-rows

DECLARE @input STRING;
DECLARE @delimiter_position INTEGER;
DECLARE @delimiter STRING;

TRY DROP TABLE #output; CATCH ALL END TRY;

SET @delimiter = CHAR(13) + CHAR(10);
SET @input = 'AA' + CHAR(13) + CHAR(10) + 'CCC' + CHAR(13) + CHAR(10) + 'D';

CREATE TABLE
   #output
(
     counter AUTOINC
   , part CHAR(10)
);

SET @delimiter_position = POSITION(@delimiter IN @input);

WHILE @delimiter_position > 0 DO

  INSERT INTO #output (part) VALUES (LEFT(@input, @delimiter_position - 1));
  SET @input = RIGHT(@input, LENGTH(@input) - (@delimiter_position + LENGTH(@delimiter)) + 1);

  SET @delimiter_position = POSITION(@delimiter IN @input);
END WHILE;

INSERT INTO #output(part) VALUES (LEFT(@input, LENGTH(@input)));



-- SELECT * FROM #output

SELECT part FROM #output WHERE counter = 3;

If your newlines only consist of CHAR(10) (Unix style) you just have to change the @delimiter variable.

[former_member1315653]

Hi Jens,

thanks, your code works well for a single string but can it be adapted to work with a memo field within a table?

Best regards,

Steffen

[Former Member]

Hi Steffen,

yes, that's possible:

DECLARE @input STRING;

DECLARE @delimiter_position INTEGER;

DECLARE @delimiter STRING;

DECLARE @i INTEGER;

DECLARE @cur CURSOR AS SELECT id, text FROM #input;

TRY DROP TABLE #input; CATCH ALL END TRY;

TRY DROP TABLE #output; CATCH ALL END TRY;

SET @delimiter = CHAR(13) + CHAR(10);

CREATE TABLE

   #input

(

    id AUTOINC

  , text MEMO

);

CREATE TABLE

   #output

(

     id INTEGER

   , counter INTEGER

   , part CHAR(10)

);

INSERT INTO #input (text) VALUES ('AA' + CHAR(13) + CHAR(10) + 'CCC' + CHAR(13) + CHAR(10) + 'D');

INSERT INTO #input (text) VALUES ('23' + CHAR(13) + CHAR(10) + '333' + CHAR(13) + CHAR(10) + '7');

OPEN @cur;

WHILE FETCH @cur DO

SET @input = @cur.text;

SET @i = 1;

SET @delimiter_position = POSITION(@delimiter IN @input);

WHILE @delimiter_position > 0 DO

  INSERT INTO #output (id, counter, part) VALUES (@cur.id, @i, LEFT(@input, @delimiter_position - 1));

  SET @input = RIGHT(@input, LENGTH(@input) - (@delimiter_position + LENGTH(@delimiter)) + 1);

  SET @delimiter_position = POSITION(@delimiter IN @input);

  SET @i = @i + 1;

END WHILE;

INSERT INTO #output(id, counter, part) VALUES (@cur.id, @i, LEFT(@input, LENGTH(@input)));

END WHILE;

CLOSE @cur;

SELECT * FROM #output

[former_member1315653]

Hi Jens,

works great, thank you very much / Dankeschön !!!

Best regards,

Steffen

[Former Member]

Hi Steffen,

you're welcome / gerne