Re: Oracle 10g Question
Date: Thu, 24 Jan 2008 10:32:02 -0800 (PST)
On Jan 24, 12:21 pm, "fitzjarr..._at_cox.net" <fitzjarr..._at_cox.net> wrote:
> 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:
> cursor get_vals is
> with t as (
> select '1:3,4:6,8:10,3:4,7:6,11:12' z from dual
> 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;
> for a in get_vals loop
> insert into mytable
> values (a.first, a.second);
> end loop;
> David Fitzjarrell
That works pretty good. Again, I'll have to read up on 'with', 'connect by' and now 'level' as I do not know what the heck those commands do...... Received on Thu Jan 24 2008 - 12:32:02 CST