Re: REGEXP_REPLACE doesn't comply with the standard, does it ?
Date: Tue, 29 Jun 2010 11:04:43 -0700 (PDT)
Message-ID: <f671f810-db7e-4724-a07f-56e8aff6ccf4_at_z10g2000yqb.googlegroups.com>
On Jun 29, 4:06 pm, Spendius <spend..._at_muchomail.com> wrote:
> Hi,
>
> The following works *everywhere* I've ran it:
> $ echo rbcde_fghijkl_mnopq|sed "s/_\([a-z]\)/\u\1/g"
> rbcdeFghijklMnopq
>
> (in vi, at several Unix prompts...: I want to replace letters that
> come after an underscore by their uppercase value, and get
> rid of this underscore)
>
> I've found no way to have the same behaviour using REGEXP_REPLACE:
> SQL> select regexp_replace('rbcde_fghijkl_mnopq','_\([a-z]\)','\u\1')
> from dual;
> REGEXP_REPLACE('RBC
> -------------------
> rbcde_fghijkl_mnopq
>
> How can you then obtain in SQL what I'm getting above in Ux ?
>
> Thanks.
> Spendius
Well, not every RE engine supports these escape sequences as they are actually not 'standard'. Most Perl-derived do, but POSIX standard for RE does not define them and Oracle's implementation conforms to POSIX with just a few Perl-influenced extensions. Unfortunately, \U, \u, \L and \l are not among them. And there seem to be no way to do what you want with REGEXP_REPLACE, at least I don't see how it can be achieved. Java Pattern class does not support these escapes, too, so you can't even resort to built-in Java VM.
You might submit an enhancement request to Oracle and ask them to implement these escapes, but my experience with Oracle ERs suggests that even if they will consider it for implementation we are unlikely to see it actually implemented until R12.2.
Regards,
Vladimir M. Zakharychev Received on Tue Jun 29 2010 - 13:04:43 CDT