Re: Oracle 10g Question

amerar_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

6 rows selected.

Best regards

Maxim Received on Thu Jan 24 2008 - 11:59:02 CST