Re: Oracle 10g Question

"Maxim Demenko" <mdemenko_at_gmail.com> schreef in bericht news:4798D1E6.1000404_at_gmail.com...
> 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
> 11 12
>
> 6 rows selected.
>
> Best regards
>
> Maxim

Wow!

Shakespeare Received on Thu Jan 24 2008 - 12:12:11 CST