Link to home
Start Free TrialLog in
Avatar of ramesh_73
ramesh_73

asked on

Substring based on a delimiter

I have the string structure as "l0adad@15-apr-1999@success@select count(*) from dual"
In this I need to extract string separated by the delimiter '@'.  For example in this case I need to extract l0adad
15-apr-1999
success
and select count(*) from dual
into separate variables.  Could anyone help me in suggesting a way for this.  Do you have any direct functions for doing this?
Avatar of sbenyo
sbenyo

How exactly do you get this structure ?

Do you need a PL/SQL proc to seperate the strings ?

What do you want to do with the seperated strings ?
a smple way to do it is :

ix := 1;
start := 1;
stop  := 1;

vc := "l0adad@15-apr-1999@success@select count(*) from dual"
;

while TRUE loop
  IF SUBSTR(vc,start,stop) IN ('@')  then
     vString := SUBSTR(vc,ix,stop)
     start := ix;
  END IF;
  ix := ix + 1;
  stop := ix;

end loop


something like this... in vString you receive for each time you get a "@" the hole sub-string. you can put this substrings in an array for save them...


Hope this helps
opppsss! I miss the condition to exit from the Loop... this is when the ix gets to LENGTH(vc)...

and I think you hace to replace start for ix in the IF condition...

well the point is giving you an idea...

:)


you could also try using instring function to find the position of '@' in the string and then use the substr function to extract.
Below is a package I wrote while ago to parse delimited strings:

-- ______________________________________________________________________
-- |                                                      |
-- |                        PARSING_PKG                        |
-- |____________________________________________________________________|

-- FILE:      PARSING.PKG

-- LOCATION:

-- TITLE:      Parsing Package

-- TYPE:      ORACLE PL/SQL Package

-- VERSION:      1.0

-- CREATED:      June 25, 1996

-- AUTHOR:      Solomon Yakobson

-- WARNING:

-- DESCRIPTION:      This package will  provide  all  components necessary to
--            perform various data parsing routines.

-- NOTES:

-- MODIFICATION
-- HISTORY:
-- _____________________________________________________________________

CREATE OR REPLACE PACKAGE Parsing_Pkg
  AS
  FUNCTION List_Item_Count(
                     vList            IN     VARCHAR2,
                     vListItemDelimiter      IN     VARCHAR2 DEFAULT ','
                    ) RETURN NUMBER;
  FUNCTION Get_List_Item(
                   vList                  IN     VARCHAR2,
                   vListItemDelimiter      IN     VARCHAR2 DEFAULT ',',
                   nItemNumber            IN     NUMBER
                  ) RETURN VARCHAR2;
END Parsing_Pkg;
/
SHOW ERRORS

CREATE OR REPLACE PACKAGE BODY Parsing_Pkg
  AS
--  _____________________________________________________________________
-- |                                                      |
-- |                        LIST ITEM COUNT                        |
-- |____________________________________________________________________|

-- TITLE:      List Item Count Function

-- TYPE:      Public Packaged Fuction

-- VERSION:      1.0

-- CREATED:      June 25, 1996

-- AUTHOR:      Solomon Yakobson

-- WARNING:

-- DESCRIPTION:      This function will parse a given list of items delimited
--            by a given delimiter and return number of items in  that
--            list. List must have the following format:
--
--                <item_1><delimiter><item_2><delimiter>...<item_n>
--
--            List can  not  be  empty, can  not  start with list item
--            delimiter and can not  have two list item delimiters next
--            to  each other.  Delimiter  is a character string  of any
--            length. If omitted, delimiter defaults to ','.

-- PARAMETERS:      This  function  requires  two  input parameters: list and
--            list item delimiter.

-- RETURN:      Number of list items on success, 0 if list is invalid.

-- NOTES:

-- MODIFICATION
-- HISTORY:
-- _____________________________________________________________________

  FUNCTION List_Item_Count(
                     vList            IN     VARCHAR2,
                     vListItemDelimiter      IN     VARCHAR2 DEFAULT ','
                    ) RETURN NUMBER
    IS
      nItemCount      NUMBER;
    BEGIN
      IF    vList IS NULL
         OR SUBSTR(vList,1,LENGTH(vListItemDelimiter)) = vListItemDelimiter
         OR INSTR(vList||vListItemDelimiter,
                vListItemDelimiter||vListItemDelimiter) != 0 THEN
        RETURN 0;
      END IF;
      nItemCount := 0;
      WHILE INSTR(vList||vListItemDelimiter,vListItemDelimiter,1,
                nItemCount + 1) != 0 LOOP
        nItemCount := nItemCount + 1;
      END LOOP;
      RETURN nItemCount;
    END List_Item_Count;

--  _____________________________________________________________________
-- |                                                      |
-- |                        GET LIST ITEM                        |
-- |____________________________________________________________________|

-- TITLE:      Get List Item Function

-- TYPE:      Public Packaged Fuction

-- VERSION:      1.0

-- CREATED:      June 25, 1996

-- AUTHOR:      Solomon Yakobson

-- GROUP:

-- Copyright(c)1996, Taconic Data Corporation

-- WARNING:

-- DESCRIPTION:      This function will parse a given list of items delimited
--            by a given  delimiter and return back a given list item.
--            List must have the following format:
--
--                <item_1><delimiter><item_2><delimiter>...<item_n>
--
--            This function does not validate list format. Use function
--            List_Item_Count  to validate  the list and get the number
--            of  list  items. Delimiter  is a character string  of any
--            length. If omitted, delimiter defaults to ','.

-- PARAMETERS:      This function requires three input parameters: list, list
--            item delimiter and item number.

-- RETURN:      Requested list item.

-- NOTES:

-- MODIFICATION
-- HISTORY:
-- _____________________________________________________________________

  FUNCTION Get_List_Item(
                   vList                  IN     VARCHAR2,
                   vListItemDelimiter      IN     VARCHAR2 DEFAULT ',',
                   nItemNumber            IN     NUMBER
                  ) RETURN VARCHAR2
    IS
      nItemPosition      NUMBER;
      nItemLength      NUMBER;
    BEGIN
      nItemPosition := INSTR(vListItemDelimiter||vList,
                         vListItemDelimiter,1,nItemNumber);

      nItemLength := INSTR(vList||vListItemDelimiter,vListItemDelimiter,
                       1,nItemNumber) - nItemPosition;
      RETURN SUBSTR(vList,nItemPosition,nItemLength);
  END Get_List_Item;

END Parsing_Pkg;
/
SHOW ERRORS

Solomon Yakobson.
Avatar of ramesh_73

ASKER

Hey syakobson,
I already had this in my mind. But I felt like there should be some better solution rather than this.  Sorry, don't feel bad that I rejected your answer, I am looking for a still better option.  Anyway, thanks for your help.
Regards,
Ramesh.
Avatar of kretzschmar
hmm,

what about this

select a,
       b,
       Substr(y,1,Instr(y,'@')-1) c,      
       Substr(y,Instr(y,'@')+1,length(y)) d from (
select a,
       Substr(x,1,Instr(x,'@')-1) b,      
       Substr(x,Instr(x,'@')+1,length(x)) y from (
select Substr('l0adad@15-apr-1999@success@select count(*) from dual',1,Instr('l0adad@15-apr-1999@success@select count(*) from dual','@')-1) a,
       Substr('l0adad@15-apr-1999@success@select count(*) from dual',Instr('l0adad@15-apr-1999@success@select count(*) from dual','@')+1,length('l0adad@15-apr-1999@success@select count(*) from dual')) x
 from dual))

meikl
ASKER CERTIFIED SOLUTION
Avatar of syakobson
syakobson

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
a simple pl/sql script can solve this problem :
declare
qry_str varchar2(100) := 'l0adad@15-apr-1999@success@select count(*) from dual';

i number(2) := 1;
ctr number(2) := 0;
ctr1 number(2) := 1;
startchar number(2) := 0;
noofchars number(2) := 1;
begin

loop
            ctr := instr(qry_str, '@', startchar + 1, 1);
         if (ctr > 0) then
                 dbms_output.put_line (substr(qry_str, startchar + 1, (ctr - 1) - startchar));
         else
          dbms_output.put_line (substr(qry_str, startchar + 1, length(qry_str)));
            exit;
         end if;
         startchar := ctr;
end loop;

end;
/
I not only need to parse the values but also need to store them in a variable.
Hey syakobson,
 I think yours is the one which suites my requirement. But still I need to know bit more about array declaration part before accepting yours as the answer.  Could you explain me the declaration of array.  That would be of great help.
Regards,
Ramesh.
Hey syakobson,
 I think yours is the one which suites my requirement. But still I need to know bit more about array declaration part before accepting yours as the answer.  Could you explain me the declaration of array.  That would be of great help.
Regards,
Ramesh.
well i think instead of using dbms_output, u can use an PL/SQL array or varray to  store variables using the same script i sent....
Thanks for the new idea of using arrays. That was really interesting and that solved my requirement also.