From: Spendius on
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
From: Vladimir M. Zakharychev on
On Jun 29, 4:06 pm, Spendius <spend...(a)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


From: joel garry on
On Jun 29, 5:06 am, Spendius <spend...(a)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, initcap seems to work with underscores, then you get rid of the
underscores?

1 select
replace(replace(initcap('dummy'||'rbcde_fghijkl_mnopq'),'_',''),'Dummy','')
2* from dual
JEG(a)TTST> /

REPLACE(REPLACE(I
-----------------
rbcdeFghijklMnopq

jg
--
@home.com is bogus.
http://en.wikipedia.org/wiki/Bananadine

From: Peter Nilsson on
On Jun 30, 6:44 am, joel garry <joel-ga...(a)home.com> wrote:
> On Jun 29, 5:06 am, Spendius <spend...(a)muchomail.com> wrote:
> > The following works *everywhere* I've ran it:
> > $ echo rbcde_fghijkl_mnopq|sed "s/_\([a-z]\)/\u\1/g"
> > rbcdeFghijklMnopq
<snip>
> > 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 ?
>
> Well, initcap seems to work with underscores, then you get rid
> of the underscores?
>
>   1  select
> replace(replace(initcap
> ('dummy'||'rbcde_fghijkl_mnopq'),'_',''),'Dummy','')
>   2* from dual
> JEG(a)TTST> /
>
> REPLACE(REPLACE(I
> -----------------
> rbcdeFghijklMnopq

Slightly simpler...

select str,
substr(replace(initcap('x' || str), '_', ''), 2)
from (
select 'rbcde_fghijkl_mnopq' str from dual union all
select '_xx_xx' str from dual
);

--
Peter