How to split comma separated string inside stored procedure?

Question

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

Using Firebird 2.5

Incarnadine

Answer 1

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

Airway

Just to note: it won't return any result for AINPUT like ',1,2' but it will return three substrings for AINPUT like '1,2,'. – Ardent

Answer 2

I am posting modified Michael's version, maybe it will be useful for someone.

The changes are:

1. SPLIT_STRING is a selectable procedure.
2. Custom delimiter is possible.
3. It parses also cases when delimiter is a first character in the P_STRING.

set term ^ ;
create procedure split_string (
    p_string varchar(32000),
    p_splitter char(1) ) 
returns (
    part varchar(32000)
) 
as
  declare variable lastpos integer;
  declare variable nextpos integer;
begin
    p_string = :p_string || :p_splitter;
    lastpos = 1;
    nextpos = position(:p_splitter, :p_string, lastpos);
    if (lastpos = nextpos) then
        begin
            part = substring(:p_string from :lastpos for :nextpos - :lastpos);
            suspend;
            lastpos = :nextpos + 1;
            nextpos = position(:p_splitter, :p_string, lastpos);
        end
    while (:nextpos > 1) do
        begin
            part = substring(:p_string from :lastpos for :nextpos - :lastpos);
            lastpos = :nextpos + 1;
            nextpos = position(:p_splitter, :p_string, lastpos);
            suspend;
        end
end^
set term ; ^

Ardent

Great job @MartjinPieters :) – Tret

Answer 3

Use POSITION

and

SUBSTRING

functions in a WHILE DO statement

Mystery

Welcome to Stack Overflow! Whilst this may theoretically answer the question, it would be preferable to include the essential parts of the answer here, and provide the link for reference. Thanks – Version

Answer 4

A similar solution I use, published a while ago by Jiri Cincura http://blog.cincura.net/232347-tokenize-string-in-sql-firebird-syntax/

recreate procedure Tokenize(input varchar(1024), token char(1))
returns (result varchar(255))
as
declare newpos int;
declare oldpos int;
begin
  oldpos = 1;
  newpos = 1;
  while (1 = 1) do
  begin
    newpos = position(token, input, oldpos);
    if (newpos > 0) then
    begin
      result = substring(input from oldpos for newpos - oldpos);
      suspend;
      oldpos = newpos + 1;
    end
    else if (oldpos - 1 < char_length(input)) then
    begin
      result = substring(input from oldpos);
      suspend;
      break;
    end
    else
    begin
      break;
    end
  end
end

Cyndicyndia

Answer 5

It looks good except one thing, in my Firebird server Varchar size declaration to 32000 cause "Implementation limit exceeded" exception so be careful. I suggest to use BLOB SUB_TYPE TEXT instead :)

Horse

Answer 6

This works for me on an Informix DataBase:

DROP FUNCTION rrhh:fnc_StringList_To_Table;
CREATE FUNCTION rrhh:fnc_StringList_To_Table (pStringList varchar(250))
    RETURNING INT as NUMERO;
    
    /* A esta Funcion le podes pasar una cadena CSV con una lista de numeros
     *      Ejem:  EXECUTE FUNCTION fnc_StringList_To_Table('1,2,3,4');
     * y te devolvera una Tabla con dichos numeros separados uno x fila
     * Autor:  Jhollman Chacon - 2019 */
     
    DEFINE _STRING VARCHAR(255);
    DEFINE _LEN INT;
    DEFINE _POS INT;
    DEFINE _START INT;
    DEFINE _CHAR VARCHAR(1);
    DEFINE _VAL INT;
    
    LET _STRING = REPLACE(pStringList, ' ', '');
    LET _START = 0;
    LET _POS = 0;
    LET _LEN = LENGTH(_STRING);

    FOR _POS = _START TO _LEN
        LET _CHAR = SUBSTRING(pStringList FROM _POS FOR 1);
        
        IF _CHAR <> ',' THEN 
            LET _VAL = _CHAR::INT;
        ELSE 
            LET _VAL = NULL;
        END IF;
        
        IF _VAL IS NOT NULL THEN 
            RETURN _VAL WITH RESUME;
        END IF;
        
    END FOR;
    
END FUNCTION;

EXECUTE FUNCTION fnc_StringList_To_Table('1,2,3,4');
SELECT * FROM TABLE (fnc_StringList_To_Table('1,2,3,4'));

Mud

You saved my life, Thank you very much, I've been trying to find something like this which could work for Informix. – Waive