Re: Cannot upper a string in a regex_replace

"Wabiloo" <fabre.lambeau_at_gmail.com> wrote in message news:1149074612.835488.66480_at_f6g2000cwb.googlegroups.com...
> Using oracle 10g (10.2) on a Windows 2003 server
>
> I'm trying to convert a comma-separated list of alphanumeric strings
> into a carriage-return-separated list of strings with bullet points and
> first letter as uppercase.
>
> eg. "blue car,red rose,Yellow canari,radio control CD,massive, long,
> string"
> should become
> "- Blue car
> - Red rose
> - Yellow canari
> - Radio control CD
> - Massive, long, string"
>
> As shown, the replacement only occurs when the comma is not followed by
> a space.
>
> To do this, I have the following expression in my select statement:
> '-' || ' ' || regexp_replace ( col_features ,
> ',([^[:space:]])' ,
> chr(13) || chr(149) || ' ' ||
> upper('\1') )
>
> However, although the backreference ('\1') is working correctly (ie. I
> don't lose the non-space character following a space), the upper
> function does not seem to work. Indeed, what I get is:
> "- blue car
> - red rose
> - Yellow canari
> - radio control CD
> - massive, long, string"
>
> If I surround the entire expression with upper(), I get every character
> uppercase, as expected, so this tells me that the function works. I
> therefore get the impression that it is to do with the backreference
> used in combination with the upper() function.

Of course this won't work as you expect: upper() is applied to literal string '\1' effectively leaving it intact in replacement string. Your expression is equivalent to

regexp_replace(col_features,',([^[:space:]])',chr(13)||chr(149)||'\1')

You can only apply a function to the result of regexp_replace(). It could be InitCap(), but this function will capitalize each initial letter in every word, not quite what you need.

-- 
   Vladimir M. Zakharychev
   N-Networks, makers of Dynamic PSP(tm)
   http://www.dynamicpsp.com