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?
ramesh_73Asked:
Who is Participating?
 
syakobsonConnect With a Mentor Commented:
Not sure what you mean by "better solution". No matter what you have to parse the string. To assign parsed portions of the string to separate variables, use PL/SQL table:

CREATE OR REPLACE
PACKAGE TypeDefinitions
IS
TYPE VC100_TBL_TYPE IS TABLE OF VARCHAR2(100)
INDEX BY BINARY_INTEGER;
END TypeDefinitions;
/
DECLARE
Element VC100_TBL_TYPE;
ElementCount NUMBER;
List VARCHAR2(4000) := 'l0adad@15-apr-1999@success@select count(*) from dual;
BEGIN
ElementCount := Parsing_Pkg.List_Item_Count(List,'@');
FOR I IN 1..ElementCount LOOP
Element(I) := Parsing_Pkg.Get_List_Item(List,'@',I);
END LOOP;
END;
/

Solomon Yakobson.
0
 
sbenyoCommented:
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 ?
0
 
NetoManCommented:
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
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
NetoManCommented:
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...

:)


0
 
sudhi022299Commented:
you could also try using instring function to find the position of '@' in the string and then use the substr function to extract.
0
 
syakobsonCommented:
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.
0
 
ramesh_73Author Commented:
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.
0
 
kretzschmarCommented:
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
0
 
sohillCommented:
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;
/
0
 
ramesh_73Author Commented:
I not only need to parse the values but also need to store them in a variable.
0
 
ramesh_73Author Commented:
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.
0
 
ramesh_73Author Commented:
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.
0
 
sohillCommented:
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....
0
 
ramesh_73Author Commented:
Thanks for the new idea of using arrays. That was really interesting and that solved my requirement also.  
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.