Re: Oracle 10g Question

From: <fitzjarrell_at_cox.net>
Date: Thu, 24 Jan 2008 10:11:43 -0800 (PST)
Message-ID: <fe9758f0-77a5-4c4b-b298-15dcd4f90eb0@i3g2000hsf.googlegroups.com>


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- Hide quoted text -
>
> - Show quoted text -

I suppose I need to play with this a bit more. :)

Nice example.

David Fitzjarrell Received on Thu Jan 24 2008 - 12:11:43 CST

Original text of this message