Hi,

I wan't to convert a string "bygger'n" to "Bygger'n", is it possible to do something like this with regexp_replace?

"bygger'n" is just meant as an example, I want to replace all matching patterns with lowercase letters.

Oracle does not seem to consider lower/upper-functions for the replace of patterns:

SQL> select regexp_replace(initcap('bygger''n'), '(''[[:alnum:]])', lower('\1')) try_lower

  2  ,      regexp_replace(initcap('bygger''n'), '(''[[:alnum:]])', ' \1 ') spaces

  3  from   dual

  4  /



TRY_LOWE SPACES


----------

Bygger'N Bygger 'N

Regards
Haavard

@damorgan:
I don't think case sensitivity is going to help me, perhaps explaining the problem properly will

The problem: Formatting names from uppercase:

Source	       Formatted



O'MALLEY       O'Malley

TEH'TE         Teh'te

BYGGER'N       Bygger'n

A single quote placed 2nd in the name should be followed by an upprecase character, placed anywhere else it should be followed by a lowercase character.

So; can this be solved by regexp_replace?

I realise now that in my original question the lower('\1') will only perform lower-function to '\1', not the actual result of the back reference.

Can the text in a back reference in the replace_string of a regexp_replace-function be manipulated in any way?

Regards,
Haavard

@cd
Thank you for your clarification. REGEXP_INITCAP would help in this particular case, REGEXP_REPLACE with all the normal Oracle string manipulating functions available for manipulating the back reference would be even better

In the mean while:

SQL> select original

  2  ,      regexp_replace(

  3           initcap(

  4              regexp_replace( original

  5                            , '([[:alnum:]]{2,})('')([[:alnum:]*])'

  6                            , '\199dummystring99\3'))

  7          , '99dummystring99'

  8          , '''') formatted

  9  from   (select 'O''MALLEY' original from dual

 10          union

 11          select 'TEH''TE' original from dual

 12          union

 13          select 'BYGGER''N' original from dual);


 ORIGINAL FORMATTED


--------------------

 BYGGER'N Bygger'n

 O'MALLEY O'Malley

 TEH'TE   Teh'te

It's not pretty, but works for my purposes. Well, at least until there comes a person called "99dummystring99". Just a question of time

Regards,
Haavard

@cd
Thank you! I'm only coming to grasp the regexp syntax, and I never would have thought to dissect the string like that.

Very educational, and Mr. 99dummystring99 will also receive a letter with nicely formatted name

Shouldn't it be McDonald?

C.

@Volder
Yeah, that was what I intended...

Ok, let's up the ante; replace the <magic regexp thingy> to achieve the following in the formatted column based on the source column:

with t as (select 'FIRSTNAME O''MALLEY' col1

             from dual

            union

           select 'FIRST''NAME  TEH''TE'

             from dual

            union 

           select 'FORMAT ME BYGGER''N'

             from dual

            union 

           select 'OLD MCDONALD'

             from dual

            union

           select 'EVEN OL''DER MACDONALD'

             from dual

           )

select t.col1 source 

     , <magic regexp thingy> formatted

  from t

; 


SOURCE                FORMATTED


---------------------

EVEN OL'DER MACDONALD Even Ol'der MacDonald

FIRST'NAME  TEH'TE    First'name  Teh'te

FIRSTNAME O'MALLEY    Firstname O'Malley

FORMAT ME BYGGER'N    Format Me Bygger'n

OLD MCDONALD          Old McDonald

Bearing in mind that:
- String SOURCE contains 1..x names separated by space
- Formatting rules must be applied to all names in the string

I can be as precise as you want in specifying the rules for the formatted column, but in the spirit of the scrum age that is upon us, I'll settle for: Please, don't hesitate to ask if anything is unclear

Regards,
Havard

NB: I see that this post could result in "Do you want US to do all YOUR work?", but I'll take my chances:) Anyway, I think the result might be of an interest to more than me.

If you are on 10g you can do it with a Java Stored Procedure that handles regular expressions with a powerful API. Take a look at the Matcher class.

http://www.regular-expressions.info/java.html
http://www.javaworld.com/javaworld/jw-02-2003/jw-0207-java101.html

create or replace java source named MyInitcap as
import java.util.regex.*;
 
class MyInitcap {
	public static String firstUpper(String s) {
		return Character.toUpperCase(s.charAt(0))+s.substring(1).toLowerCase();
	}
	public static String initcap(String input) {
		
		Pattern p = Pattern.compile ("((\\w|')+)");
		Matcher m = p.matcher (input);
		StringBuffer sb = new StringBuffer ();
		while (m.find ())
			m.appendReplacement (sb, firstUpper(m.group()));
		m.appendTail (sb);
		return sb.toString();
	}
}
/ 
 
create or replace function my_initcap (
		string in varchar2
	)return varchar2 
as language java name 'MyInitcap.initcap(java.lang.String) return java.lang.String';
/ 

And then

Processing ...

with t as (select 'FIRSTNAME O''MALLEY' col1

             from dual

            union

           select 'FIRST''NAME  TEH''TE'

             from dual

            union 

           select 'FORMAT ME BYGGER''N'

             from dual

            union 

           select 'OLD MCDONALD'

             from dual

            union

           select 'EVEN OL''DER MACDONALD'

             from dual

           )

select t.col1 source 

     , my_initcap(col1) formatted

  from t

Query finished, retrieving results...

        SOURCE              FORMATTED

------------------------

EVEN OL'DER MACDONALD Even Ol'der Macdonald   

FIRST'NAME  TEH'TE    First'name  Teh'te     

FIRSTNAME O'MALLEY    Firstname O'malley      

FORMAT ME BYGGER'N    Format Me Bygger'n      

OLD MCDONALD          Old Mcdonald           



5 row(s) retrieved

Bye Alessandro

@cd
"Quick hack" being the understatement of the century But it works beautifully!

@Rossi
For me this is pl/sql food; I am familiar with the language (more than the coffee), and most logic is already in pl/sql. I can't see what hiding the logic behind a function call in one or the other differs, other than in the actual implementation.

But I agree with the strategy; hide the logic behind a function, and I am sure so does cd. This is not the kind of sql you want to maintain in every query... Although, in a thread like this, the sql is very simple to communicate and reproduce/enhance.