-- Rem
-- Rem Script Description: The Strtok Functions Parses An Input String Separated By Any Character
-- Rem (Which Acts As A Separator), And Returns The Tokens One By One. After
-- Rem A First Call, The Next Token Is Found With The Null String As Input.
-- Rem The Separator String May Be Modified For Subsequent Calls. When No More
-- Rem Tokens Are Found, The Function Returns Null.
-- Rem
-- Rem Prepared By: Oracle Resource Stop
-- Rem
-- Rem Usage Information: See Below
-- Rem
-- Rem
CREATE OR REPLACE PACKAGE PARSE_STRING IS
-- Rem Package User-Invisible String
QUEUE_STRTOK VARCHAR2(1000);
-- Rem Package User Function
FUNCTION STRTOK (CHAIN IN VARCHAR2, DELMTR IN VARCHAR2) RETURN VARCHAR2;
-- Rem Package Hidden Function
FUNCTION HIDDEN_STRTOK (CHAIN IN VARCHAR2, DELMTR IN VARCHAR2) RETURN VARCHAR2;
-- Rem Package Left-Trim Function
FUNCTION LTRIM_DELMTR(CHAIN VARCHAR2, DELMTR VARCHAR2) RETURN VARCHAR2;
-- Rem End_Package_Parse_string
END;
/
CREATE OR REPLACE PACKAGE BODY parse_string IS
-- Rem User Invisible Function
FUNCTION HIDDEN_STRTOK (CHAIN IN VARCHAR2, DELMTR IN VARCHAR2)
RETURN VARCHAR2 IS
TEMP VARCHAR2(1000);
LGTH INTEGER;
POS INTEGER;
CAR CHAR(1);
BEGIN
-- Rem End_Function_Hidden_Strtok
TEMP :=LTRIM_DELMTR(CHAIN, DELMTR);
LGTH := LENGTH(TEMP);
FOR COUNTER IN 1..LGTH LOOP
CAR := SUBSTR(TEMP,COUNTER,1);
POS := INSTR(DELMTR, CAR);
IF POS <> 0 THEN
QUEUE_STRTOK := SUBSTR(TEMP, COUNTER+1);
RETURN (SUBSTR(TEMP, 1, COUNTER-1));
END IF;
END LOOP;
QUEUE_STRTOK := NULL;
RETURN (TEMP);
EXCEPTION
WHEN OTHERS THEN
RETURN(NULL);
-- Rem End_Function_Hidden_Strtok
END;
-- Rem Package User Function
FUNCTION STRTOK (CHAIN IN VARCHAR2, DELMTR IN VARCHAR2)
RETURN VARCHAR2 IS
TEMP VARCHAR2(1000);
BEGIN
-- Rem Begin_Function_Strtok
-- Rem If Delimiter Null, Return Last Chain Found Or The Initial String
IF DELMTR IS NULL
THEN
IF QUEUE_STRTOK IS NULL
THEN
RETURN (CHAIN);
ELSE
RETURN (QUEUE_STRTOK);
END IF;
END IF;
-- Rem If, Delimiter Not Null
IF CHAIN IS NULL
THEN
IF QUEUE_STRTOK IS NULL
THEN
RETURN (NULL);
ELSE
TEMP := HIDDEN_STRTOK(QUEUE_STRTOK, DELMTR);
RETURN (TEMP);
END IF;
ELSE
QUEUE_STRTOK := NULL;
TEMP := HIDDEN_STRTOK(CHAIN, DELMTR);
RETURN (TEMP);
END IF;
-- Rem End_Function_Strtok
END;
FUNCTION LTRIM_DELMTR(CHAIN VARCHAR2, DELMTR VARCHAR2)
RETURN VARCHAR2 IS
TEMP VARCHAR2(1000);
LGTH INTEGER;
POS INTEGER;
CAR CHAR(1);
BEGIN
-- Rem Begin_Function_Ltrim_Delmtr
TEMP := CHAIN;
LGTH := LENGTH(TEMP);
FOR COUNTER IN 1..LGTH LOOP
CAR := SUBSTR(TEMP,COUNTER,1);
POS := INSTR(DELMTR, CAR);
IF POS = 0 THEN
RETURN (SUBSTR(TEMP, COUNTER));
END IF;
END LOOP;
RETURN (NULL);
-- Rem End_Function_Ltrim_Delmtr
END;
-- Rem End_Package_Body_Parse_String
END;
/
-- Rem
-- *********************************************************************************************
-- Rem The Following Is A Sample Of Possible Usage For The Parse_String Procedure
-- Rem This Sample Will Identify All Defined Separators In The String
-- ********************************************************************************************
-- Rem SEPARATORS := '- ;/';
-- Rem STRING := 'BLACK;BLUE;GREEN/YELLOW';
-- Rem ...
-- Rem TOKEN := PARSE_STRING.STRTOK (STRING, SEPARATORS);
-- Rem WHILE TOKEN IS NOT NULL LOOP
-- Rem TOKEN := PARSE_STRING.STRTOK (NULL, SEPARATORS);
-- Rem END LOOP;
-- Rem ...
-- Rem
On Wednesday, March 02, 2005, at 06:54AM, Anthony Molinaro <oracledba-ezmlmshield-x64251675.[Email address protected] wrote:
>T.J,
>
> There's a few ways to do this, here's one:
>
>SQL> select ',1, 2, 3, 4, 5, 6,' as v_name from dual;
>
>V_NAME
>------------------
>,1, 2, 3, 4, 5, 6,
>
>
>SQL> select to_number(
> 2 ltrim(
> 3 substr(x.v_name,
> 4 instr(x.v_name,',',1,iter.pos),
> 5
>instr(x.v_name,',',1,iter.pos+1)-instr(x.v_name,',',1,iter.pos)),','))
>v_name
> 6 from (select ',1, 2, 3, 4, 5, 6,' as v_name from dual) x,
> 7 (select rownum pos from emp) iter
> 8 where iter.pos <=
>length(x.v_name)-length(replace(x.v_name,','))-1;
>
> V_NAME
>----------
> 1
> 2
> 3
> 4
> 5
> 6
>
>SQL> select to_number(
> 2 ltrim(
> 3 substr(x.v_name,
> 4 instr(x.v_name,',',1,iter.pos),
> 5
>instr(x.v_name,',',1,iter.pos+1)-instr(x.v_name,',',1,iter.pos)),','))
>v_name
> 6 from (select ',1, 2,' as v_name from dual) x,
> 7 (select rownum pos from emp) iter
> 8 where iter.pos <= length(x.v_name)-length(replace(x.v_name,','))-1;
>
> V_NAME
>----------
> 1
> 2
>
>
>SQL> select to_number(
> 2 ltrim(
> 3 substr(x.v_name,
> 4 instr(x.v_name,',',1,iter.pos),
> 5
>instr(x.v_name,',',1,iter.pos+1)-instr(x.v_name,',',1,iter.pos)),','))
>v_name
> 6 from (select ',2,4,6,' as v_name from dual) x,
> 7 (select rownum pos from emp) iter
> 8 where iter.pos <= length(x.v_name)-length(replace(x.v_name,','))-1;
>
> V_NAME
>----------
> 2
> 4
> 6
>
>the idea is to use a pivot table (a cartesian) to generate n rows
>(n = the numbers of values in the string), then use substr and instr
>to parse them out.
>Once you get the values into rows, you can bulk collect into an array
>or use a for loop.
>
>Ofcourse, you could always just use a for loop and substr through each
>char of the string.
>
>Hope that helps,
> Anthony
>
>
>-----Original Message-----
>From: Randall TJ
>[mailto:oracledba-ezmlmshield-x78803132.[Email address protected]
>Sent: Wednesday, March 02, 2005 9:42 AM
>To: LazyDBA Discussion
>Subject: Parsing a comma-delimited string
>
>I have an alarm string that is sometimes comma delimited - some examples
>are: '1', '1, 2', '1, 2, 3, 4, 5, 6' (There could be one alarm, and up
>to six alarms, spaced as shown in the example).
>
>I can hard code the substr code like:
>
> v_name := '1, 2, 3, 4, 5, 6';
>
> SELECT substr( v_name,1, 1 ) alarm1,
> substr( v_name,4, 1 ) alarm2,
> substr( v_name,7, 1 ) alarm3,
> substr( v_name,10, 1 ) alarm4,
> substr( v_name,13, 1 ) alarm5,
> substr( v_name,16, 1 ) alarm6
> INTO v_First, v_Second, v_Third,v_Fourth,v_Fifth,v_Sixth
> from dual;
>
>But my spidey senses are on high, since the engineers swear that the
>alarms cannot be transmitted in any other way. I would like to find a
>way to parse the alarms based on comma position.
>
>Any help would be greatly appreciated!
>
>Thank you.
>
>T.j.
>
>
>
>--------
>website: http://www.LazyDBA.com
>Please don't reply to RTFM questions
>Oracle documentation is here: http://tahiti.oracle.com
>To unsubscribe: see http://www.lazydba.com/unsubscribe.html
>To subscribe: see http://www.lazydba.com
>By using this list you agree to these
>terms:http://www.lazydba.com/legal.html
>
>
>
>--------
>website: http://www.LazyDBA.com
>Please don't reply to RTFM questions
>Oracle documentation is here: http://tahiti.oracle.com
>To unsubscribe: see http://www.lazydba.com/unsubscribe.html
>To subscribe: see http://www.lazydba.com
>By using this list you agree to these terms:http://www.lazydba.com/legal.html
>
>
>
Oracle LazyDBA home page