Home » SQL & PL/SQL » SQL & PL/SQL » regular expressions
regular expressions [message #231947] Wed, 18 April 2007 22:09 Go to next message
Messages: 16
Registered: September 2006
Junior Member
hi all,

just wondering if somebody can give me a hand on creating a regular expression to lower case letters that are in capitals in a given sentence.

For example, if my string is something like this:

'My child'S best friend, Matt goes to the Children'S Wish Foundation and wasn'T hungry.'

So, I'm trying to find letters that appear after an apostrophe and lowercase them.

So far, this is what I have but it only finds the first occurrence, in this case 'S.

regexp_replace(in_string, '(''[[:upper:]])+?', lower(regexp_substr(in_string, '''([[:upper:]])+?')))

anybody have a better and accurate suggestions?

Re: regular expressions [message #232111 is a reply to message #231947] Thu, 19 April 2007 08:25 Go to previous messageGo to next message
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
This sounds an awful lot like a homework assignment.

I would suggest reading the following page as a first step. Second, try and write one that matches what you want to change. Don't worry about changing it first, just worry about matching it. Then you can add the change.


Regular expressions are a world of their own in some ways.

[Updated on: Thu, 19 April 2007 08:30]

Report message to a moderator

Re: regular expressions [message #232248 is a reply to message #232111] Thu, 19 April 2007 22:23 Go to previous message
Messages: 3727
Registered: October 2005
Location: Melbourne, Australia
Senior Member
I'm not so certain it is homework, although the example sentence certainly makes it look that way.

I don't know that it's trivial enough for trial and error either; for that to work, you have to have some candidate syntax in mind.

The issue is about replacing a RE with a lower-case version of itself. In Unix tools, this is easily done with the \L prefix to the backref modifier. eg. s/('[A-Z])/\L\1/g

But there doesn't seen to be any equivalent to the \U and \L modifiers in the POSIX standard that Oracle follows. I not only looked through the REGEXP_REPLACE() doco, but the Regular Expression links as well, and some searches on POSIX REs

Now I'm not saying there is no elegant solution, but without the \U and \L backref modifiers, I just can't seem to come up with one, nor can I find one in the doco.

So I'm sticking up for the OP here. I figure if I can't do it (elegantly, that is; you could always use a PL/SQL function) then it's an unreasonably difficult homework assignment unless of course there is something very obvious I have missed (wouldn't be the first time).

That being the case, if anyone has an elegant solution, I encourage them to post it here for the rest of the community to learn from.

Ross Leishman
Previous Topic: Preventing sql statement echo at end of spool file
Next Topic: Help with the stored proc
Goto Forum:

Current Time: Sun Feb 19 19:49:02 CST 2017

Total time taken to generate the page: 0.04541 seconds