Re: Oracle 10g Question

On Jan 24, 12:10 pm, "ame..._at_iwc.net" <ame..._at_iwc.net> wrote:
> On Jan 24, 11:59 am, Maxim Demenko <mdeme..._at_gmail.com> wrote:
>
>
>
>
>
> > ame..._at_iwc.net schrieb:
>
> > > Hi All,
>
> > > I'm playing with Oracle 10g Regular Expressions.  I have a string that
> > > looks like this, basically a comma delimited string:
>
> > > z      VARCHAR2(40) := '1:3,4:6,8:10,3:4,7:6,11:12';
>
> > > What I want to do is basically parse this string.  This this example,
> > > the 1 would go into the first variable, then the 3 into the second
> > > variable, and same for the rest of the string.
>
> > > I know Oracle has some comma_to_table routine, but I want to try this
> > > using these regular expressions.   I was using something like this
> > > with no luck:
>
> > > x := REGEXP_SUBSTR(z,'[^:]+', 1, 1);
>
> > > This returns 1 for the result, which is correct for the first item,
> > > but that is as far as I can get.
>
> > > So, I want to parse this string using a comma as the delimiter between
> > > pairs, and then a colon within that to get the 2 values for the
> > > variables.
>
> > > Can this be done using the REGEXP_SUBSTR function???
>
> > > Thanks!
>
> > Maybe not exactly what you expect, but you can try to play around...
>
> > SQL> with t as (
> >    2  select '1:3,4:6,8:10,3:4,7:6,11:12' z from dual
> >    3  )
> >    4  select
> >    5  rtrim(regexp_substr(z,'\d+:',1,level),':') first,
> >    6  ltrim(regexp_substr(z,':\d+',1,level),':') second
> >    7  from t
> >    8  connect by level<=length(z)-length(replace(z,',')) + 1
> >    9  /
>
> > FIRST SECOND
> > ----- ------
> > 1     3
> > 4     6
> > 8     10
> > 3     4
> > 7     6
> > 11    12
>
> > 6 rows selected.
>
> > Best regards
>
> > Maxim
>
> Yours is interesting.  I'll have to do some reading.   I have never
> seen the 'with' command, and, I really do not know what 'connect by'
> does.
>
> Also, I'll have to implement this into a PL/SQL routine and for each
> pair, insert them into a table.....
>
> More investigation.......- Hide quoted text -
>
> - Show quoted text -

This may give you a start. Using Maxim's query as a starting point:

declare

   cursor get_vals is
   with t as (
   select '1:3,4:6,8:10,3:4,7:6,11:12' z from dual    )
   select
   rtrim(regexp_substr(z,'\d+:',1,level),':') first,    ltrim(regexp_substr(z,':\d+',1,level),':') second    from t
   connect by level<=length(z)-length(replace(z,',')) + 1; begin

   for a in get_vals loop

           insert into mytable
           values (a.first, a.second);

David Fitzjarrell Received on Thu Jan 24 2008 - 12:21:46 CST