asked on

Oracle replace string question

Could you please suggest how can I replace a string like the followings:

Something..bIgdOg..sMALlcaT..SomethingMore
Something..bIgdoG..sMaLlcaT..SomethingMore
Something..bIGDog..smaLLcat..SomethingMore

with the following:
Something..BigDog..SmallCat..SomethingMore

Using a oracle select statement with regex_replace or replace queries that is small (readable)? The input string has random upper and lower cases for the words BigDog and SmallCat.

Thank you.

Steve Wales

There is a builtin function called INITCAP that will capitalize each word:

SQL> select initcap('SmallCAT..BigDog') from dual;

INITCAP('SMALLCA
----------------
Smallcat..Bigdog

Open in new window

That's great and all, but what rule would you use to set the capital in the middle of the word since that's kinda arbitrary given the examples above

toooki

ASKER

Thank you. But I cannot change the rest of the string (Something and SomethingMore and these contents are unknown. So Initcap will not work well.

I could repeatedly use the replace statement for all combination of the SmallCAT and BigDog strings with cap/small letter mix but that will look the query unreadable.

ASKER CERTIFIED SOLUTION

Sean Stuber

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

awking00

Without a means of identifying that a string is made up of separate words (i.e. separated by underscores or slashes or such), I don't think this can be accomplished. Except for the case, are the strings always the same or could something like rEaltInYMousE also exist?

toooki

ASKER

Thank you!
The query
select
regexp_replace(
regexp_replace(yourcolumn,'[bB][iI][gG][dD][oO][gG]','BigDog'),
'[sS][mM][aA][lL]{2}[cC][aA][tT]','SmallCat')
from yourtable
perfectly worked for me. Many thanks!

awking00

So the part of the string that needs to be replaced is always the same (i.e. BigDog and SmallCat)?