Re: Oracle 10g Question

On Jan 24, 10:51 am, "ame..._at_iwc.net" <ame..._at_iwc.net> wrote:
> 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!

This is one way:

SQL> declare

  2     x       varchar2(2);
  3     y       varchar2(2);
  4     z      VARCHAR2(40) := '1:3,4:6,8:10,3:4,7:6,11:12';
  5  begin
  6     x := REGEXP_SUBSTR(z,'[^:]+', 1, 1);
  7     y := regexp_substr(z,'[^,]+', 3, 1);
  8     dbms_output.put_line(x||' '||y);

PL/SQL procedure successfully completed.

You'll likely need the regexp_instr function as well, to position the starting point of subsequent searches correctly. Knowing it's a regular pattern, with known characters, I'd be using substr() and instr() to 'walk' the string and extract values, but experimentation can be fun.

David Fitzjarrell

SQL> edit Received on Thu Jan 24 2008 - 11:59:27 CST