Parse String in Stored Procedure
I need to create a stored procedure that parses an 8-character string, and determine if a dash (-) characters exist in this string. If a dash character exist in the string, I would like to delete it. Anyone have an experience do this or an example. Appreciate all of your help. Thanks in advance. Russ Russell L. Cook Marconi Integrated Systems, Inc (858) 592-5008 russell.cook@Marconi Integrated Systems, Inc
If your using 7.3 or above, you have new built-in functions: REPLACE and SUBSTR. -- Bashar Chalabi CTL, London Cook, Russell L <Russell.Cook@marconi-is.com> wrote in message news:7qhpfj$e6m$1@news.xmission.com... > > I need to create a stored procedure that parses an 8-character string, and > determine if a dash (-) characters exist in this string. If a dash > character exist in the string, I would like to delete it. Anyone have an > experience do this or an example. Appreciate all of your help. Thanks in > advance. > > Russ > > Russell L. Cook > Marconi Integrated Systems, Inc > (858) 592-5008 > russell.cook@Marconi Integrated Systems, Inc
This is a stored procedure REPLACE I've found in the collection of SPL to
the IIUG ( thanks to Vince Nichols <vnichols@sprynet.com> )
Maybe it will help you.
Regards
Laurent LAFEUILLE llafeuil@imaginet.net
IMAGINET DESIGN
D'veloppement Base de donn'es / Web
http://www.imaginet.net
DROP PROCEDURE replace;
CREATE PROCEDURE replace(str VARCHAR(255),
search_str VARCHAR(255),
replace_str VARCHAR(255) DEFAULT NULL)
RETURNING VARCHAR(255);
DEFINE i INTEGER;
DEFINE j INTEGER;
DEFINE sw INTEGER;
DEFINE sw1 INTEGER;
DEFINE a VARCHAR(255);
DEFINE b VARCHAR(255);
DEFINE str1 VARCHAR(255);
DEFINE retstr VARCHAR(255);
DEFINE search_str1 VARCHAR(255);
DEFINE l INTEGER;
DEFINE l1 INTEGER;
DEFINE m INTEGER;
IF str IS NULL THEN
RETURN NULL;
ELSE
LET search_str1=search_str;
LET l=LENGTH(str);
LET l1=LENGTH(search_str);
IF l1>1 THEN
LET str1=str;
LET m=1;
FOR i=1 TO l
LET search_str=search_str1;
IF str[1,1]=search_str[1,1] THEN
LET a=str[1,1];
LET b=str[1,1];
LET j=1;
LET sw=0;
WHILE sw<>1
LET str1=str1[2,255];
LET search_str=search_str[2,255];
IF str1[1,1]=search_str[1,1] THEN
LET sw=0;
LET b=b||str1[1,1];
LET j=j+1;
ELSE
LET sw=1;
END IF;
IF j=l1 THEN
LET sw=1;
END IF;
END WHILE;
IF b<>search_str1 THEN
IF m=1 THEN
LET retstr=a;
ELSE
LET retstr=retstr||a;
END IF;
LET m=m+1;
ELSE
IF replace_str IS NOT NULL THEN
IF m=1 THEN
LET retstr=replace_str;
ELSE
LET retstr=retstr||replace_str;
END IF;
LET m=m+1;
END IF;
END IF;
ELSE
IF m=1 THEN
LET retstr=str[1,1];
ELSE
LET retstr=retstr||str[1,1];
END IF;
LET m=m+1;
END IF;
LET str=str1[2,255];
LET str1=str1[2,255];
END FOR;
ELSE
LET m=1;
IF str[1,1]=search_str THEN
IF replace_str IS NOT NULL THEN
LET retstr=replace_str;
LET m=m+1;
END IF;
ELSE
LET retstr=str[1,1];
LET m=m+1;
END IF;
FOR i=2 TO l
LET str=str[2,255];
IF str[1,1]=search_str THEN
IF replace_str IS NOT NULL THEN
LET retstr=retstr||replace_str;
END IF;
ELSE
IF m=1 THEN
LET retstr=str[1,1];
ELSE
LET retstr=retstr||str[1,1];
END IF;
LET m=m+1;
END IF;
END FOR;
END IF;
RETURN retstr;
END IF;
END PROCEDURE;
Cook, Russell L a 'crit dans le message <7qhpfj$e6m$1@news.xmission.com>...
>
>I need to create a stored procedure that parses an 8-character string, and
>determine if a dash (-) characters exist in this string. If a dash
>character exist in the string, I would like to delete it. Anyone have an
>experience do this or an example. Appreciate all of your help. Thanks in
>advance.
>
>Russ
>
>Russell L. Cook
>Marconi Integrated Systems, Inc
>(858) 592-5008
>russell.cook@Marconi Integrated Systems, Inc
"Cook, Russell L" wrote: > > I need to create a stored procedure that parses an 8-character string, and > determine if a dash (-) characters exist in this string. If a dash > character exist in the string, I would like to delete it. Anyone have an > experience do this or an example. Appreciate all of your help. Thanks in > advance. SPLs facility for doing this are rudimentary at best. You would have to use a brute force approach. IE: - Is char 1 a '-'? move up the rest of the string, return else is char 2 ....... Art S. Kagel
The only way is to look at each character:
create procedure nodash(a char(8)) returning char(8);define l_retchar char(8);
if a[1]='-' then
let l_retchar=a[2,8];
end if;
if a[2]='-' then
let l_retchar=a[1]||a[3,8];
end if;