MSG Endo wrote:
I have a text field VarChar(1000) with a variable number of ';' delimited strings. I wish to use SQL in a stored procedure (or function??) to extract each substring into a separate column in the results of a select statement. How to do this?
Google and Bing have not been much help so far other than a code snippet for the extraction of delimited strings.
But I am struggling with how to make code to deal with a variable number of delimited strings in the field, how to then build this code into a stored procedure which delivers a dataset of multiple rows and columns. Also not sure how to call the stored procedure in a select statement. I'm an SQL newbie so Thx in advance for your help.
Tim Crawford answers:
I recently created a selectable procedure to do this, below I found the LIST() function will do the opposite e.g. change multiple rows into a delimited string but I couldn't find anything native that does the opposite, so wrote this
Note: Was written for dialect 1, firebird 2.5, pretty extensively tested Can't recall if I have tried with FB 3.0/dialect 3 yet
set term ^ ;
create or alter procedure delimited_List_rows
(
delimited_list blob sub_type text,
delimiter varchar(1) default ','
)
RETURNS
(
list_Seq integer,
list_val blob sub_type text
)
as /* Copyright Tim Crawford 2020
Free usage with attribution
Like, don't remove this comment dude
<Desc>
Selectable procedure, returns one row per list entry
To remove duplicates, select UNIQUE list_value without list_position
<Params>
comma delimted list of words,
optional delimiter, default is comma
<Returns> One row per list entry
*/
DECLARE delimPos integer DEFAULT 1;
DECLARE delimLen INTEGER DEFAULT 1;
DECLARE emptyStr varchar(1) default '';
BEGIN
list_Seq = 1;
delimLen = COALESCE(CHAR_LENGTH(delimiter),0);
if (delimLen = 0) THEN
BEGIN
list_seq = -1;
list_val ='Invalid Delimiter parameter';
suspend;
exit;
END
-- speial case: if delim is blank and length is 1
-- replace multiple blanks with a single blank
if (delimiter = emptyStr and delimLen = 1) then
while (position(delimiter || delimiter,delimited_list) > 0) do
delimited_list = replace(delimited_list, delimiter||delimiter,delimiter);
-- remove line feeds, allows for multiline sql etc
delimited_list = replace(delimited_list,ascii_char(13),emptyStr);
delimited_list = replace(delimited_list,ascii_char(10),emptyStr);
-- get rid of leading trailing blanks, even if blank delim
delimited_list = trim(delimited_list);
-- add a delimiter at the end to make code tighter
delimited_list = delimited_list || delimiter;
while (delimited_list <> emptyStr) do
BEGIN
delimPos = POSITION(delimiter in delimited_list);
if (delimPos = 0) THEN delimited_list = emptyStr;
ELSE
BEGIN
list_val = trim(SUBSTRING(delimited_list from 1 FOR delimPos - 1));
SUSPEND;
list_Seq = list_Seq + 1;
delimited_list = SUBSTRING(delimited_list from delimPos + 1);
END
END
exit;
END^
set term ; ^
commit;
Usage examples:
select * from delimited_list_Rows('This,is,a,comma,separated list,of,values');
select * from delimited_list_Rows('This is a space separated list of values',' ');