How to split comma separated string inside stored procedure?
Solution 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
Solution 2
I am posting modified Michael's version, maybe it will be useful for someone.
The changes are:
- SPLIT_STRING is a selectable procedure.
- Custom delimiter is possible.
- 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 ; ^
Solution 3
Use 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
Solution 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
Solution 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 :)
Comments
-
Yordan Yanakiev over 3 years
How to split comma separated string into strings inside store procedure and insert them into a table field?
Using Firebird 2.5