Solved

Substring based on a delimiter

Posted on 2000-04-03
14
4,777 Views
Last Modified: 2012-06-27
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?
0
Comment
Question by:ramesh_73
  • 5
  • 2
  • 2
  • +4
14 Comments
 
LVL 5

Expert Comment

by:sbenyo
ID: 2681859
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
 
LVL 2

Expert Comment

by:NetoMan
ID: 2681909
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
 
LVL 2

Expert Comment

by:NetoMan
ID: 2681919
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
 
LVL 4

Expert Comment

by:sudhi022299
ID: 2681925
you could also try using instring function to find the position of '@' in the string and then use the substr function to extract.
0
 
LVL 4

Expert Comment

by:syakobson
ID: 2682350
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
 

Author Comment

by:ramesh_73
ID: 2683831
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
 
LVL 27

Expert Comment

by:kretzschmar
ID: 2686437
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 4

Accepted Solution

by:
syakobson earned 100 total points
ID: 2687138
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
 
LVL 1

Expert Comment

by:sohill
ID: 2689219
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
 

Author Comment

by:ramesh_73
ID: 2701358
I not only need to parse the values but also need to store them in a variable.
0
 

Author Comment

by:ramesh_73
ID: 2701384
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
 

Author Comment

by:ramesh_73
ID: 2701580
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
 
LVL 1

Expert Comment

by:sohill
ID: 2702853
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
 

Author Comment

by:ramesh_73
ID: 2711699
Thanks for the new idea of using arrays. That was really interesting and that solved my requirement also.  
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This video shows how to recover a database from a user managed backup

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now