How to split comma separated string inside stored procedure?

Yordan Yanakiev picture Yordan Yanakiev · Nov 8, 2011 · Viewed 25.6k times · Source

How to split comma separated string into strings inside store procedure and insert them into a table field?

Using Firebird 2.5

Answer

Michael picture Michael · Nov 9, 2011

Here a sample how to split the string and write the sub-strings into a table:

create procedure SPLIT_STRING (
  AINPUT varchar(8192))
as
declare variable LASTPOS integer;
declare variable NEXTPOS integer;
declare variable TEMPSTR varchar(8192);
begin
  AINPUT = :AINPUT || ',';
  LASTPOS = 1;
  NEXTPOS = position(',', :AINPUT, LASTPOS);
  while (:NEXTPOS > 1) do
  begin
    TEMPSTR = substring(:AINPUT from :LASTPOS for :NEXTPOS - :LASTPOS);
    insert into new_table("VALUE") values(:TEMPSTR);
    LASTPOS = :NEXTPOS + 1;
    NEXTPOS = position(',', :AINPUT, LASTPOS);
  end
  suspend;
end