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

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

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

Using Firebird 2.5

使用Firebird 2.5

5 个解决方案

#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

#2

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

我发布修改后的迈克尔版本,也许对某人有用。

The changes are:

变化是:

SPLIT_STRING is a selectable procedure.

SPLIT_STRING是一个可选择的过程。

Custom delimiter is possible.

自定义分隔符是可能的。

It parses also cases when delimiter is a first character in the P_STRING.

它还解析了分隔符是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 ; ^

#3

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

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

and

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

functions in WHILE DO statement

WHILE DO语句中的函数

#4

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

#5

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

#1