SQL 如何在存储过程中拆分逗号分隔的字符串？

How to split comma separated string inside stored procedure?

提问by Yordan Yanakiev

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

如何在存储过程中将逗号分隔的字符串拆分为字符串并将它们插入到表字段中？

Using Firebird 2.5

使用火鸟 2.5

回答by Michael

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

回答by Wodzu

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.

1. SPLIT_STRING 是一个可选择的过程。
2. 自定义分隔符是可能的。
3. 它还解析分隔符是 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 ; ^

回答by rstrelba

Use POSITION http://www.firebirdsql.org/refdocs/langrefupd21-intfunc-position.html

使用位置 http://www.firebirdsql.org/refdocs/langrefupd21-intfunc-position.html

and

和

SUSTRING http://www.firebirdsql.org/refdocs/langrefupd21-intfunc-substring.html

持续 http://www.firebirdsql.org/refdocs/langrefupd21-intfunc-substring.html

functions in WHILE DO statement

WHILE DO 语句中的函数

回答by Maciej Wasiak

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 :)

除了一件事，它看起来不错，在我的 Firebird 服务器 Varchar 大小声明为 32000 导致“超出实现限制”异常，所以要小心。我建议改用 BLOB SUB_TYPE TEXT :)

回答by user1300484

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

我使用的类似解决方案，不久前由 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

回答by Jhollman

This works for me on an Informix DataBase:

这在 Informix 数据库上对我有用：

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 @Cutcsa - 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'));