All Forums Database
Gupta_Pieeater 8 posts Joined 12/14
04 Nov 2015
Can REGEXP_REPLACE be used to replace multiple strings with multiple replace strings

I would like to replace the occurrence of a character in a string with an alternate character and the occurrence of a different character with a different alternate character
E,g. Replace A with Z and B with Y from ABC to return ZYC 
I know I can nest 2 REGEXP_REPLACE statements
E.G SEL REGEXP_REPLACE(REGEXP_REPLACE('ABC','A','Z',1,0,'i'),' B','Y',1,0,'i')
but is there some syntax that allows this to be coded in a single REGEXP_REPLACE statement ?

dnoeth 4628 posts Joined 11/04
04 Nov 2015

You don't need REGEXP_REPLACE in this case, to replace single characters there's
It's case specific, so for insensitive replacement you must add both upper and lower case:


Gupta_Pieeater 8 posts Joined 12/14
04 Nov 2015

Sorry Dieter, I should have been more specific, I want to replace longer strings, I just used single characters in my simple example.
I would like to change databasename.tablename to (for example) dbc.dbcinfo

MaximeV 19 posts Joined 11/13
04 Nov 2015

You can if it always follows a "pattern". It means that with the pattern provided you can divide the the source string with multiple marked subexpression.
Then you can replace those subexpressions with the desired replacements.( one subexpression always towards the same replacement).
You'll get better help if you can provide your exact case.

Gupta_Pieeater 8 posts Joined 12/14
04 Nov 2015

The source string doesn't follow a "pattern", I want to be able to replace all occurrences of one word for an alternate and replace a different word for different alternative.
so, for example,  I may have 4 strings
'help database @databasename;'
'sel count(*) from @databasename.@tablename;'
'collect statistics on @databasename.@tablename;'
'sel * from dbc.tablesv where tablename='@tablename' order by databasename'
and I would want to replace @databasename with sandpitdb and @tablename with employee i.e

'help database sandpitdb;'
'sel count(*) from sandpitdb.employee;'
'collect statistics on sandpitdb.employee;'
'sel * from dbc.tablesv where tablename='employee' order by databasename'
I was hoping there was some syntax within regexp_replace to allow multiple replaces within a single statement
something like
I know that I can do 2 quite easily by nesting 2 regexp_replace expressions but in the future I may wish to do more then 2 and was curious as to whether regexp_replace supports this approach. 
Thanks for all assistance


18 Jan 2016

Hi Everyone,
I have a slightly different scenario of searching for a string pattern that has a single quote and then to replace with a different string pattern. I attempted this by doubling single quotes everytime where was a single quote was found in the search pattern however the REGEXP function did not replace for below values. Can you suggest?
Pattern to be searched : Order( |')?[A-Z0-9]{1,2}-[A-Z0-9]{8,12}'?
Replaced with : Order xxx
SQL Tried, without any updated :
CASE WHEN REGEXP_INSTR(TRIM(BOTH FROM COLUMN1),'Order( |'')?[A-Z0-9]{1,2}-[A-Z0-9]{8,12}''?',1,1,1,'i') <> 0 THEN
REGEXP_REPLACE(COLUMN1,'ORDER( |'')?[A-Z0-9]{1,2}-[A-Z0-9]{8,12)''?','Order xxx')
ELSE 'Not Found' END AS Replaced_Code
FROM Table_Name WHERE Replaced_Code <> 'Not Found'
Project external id '1-156434343xxx'
Project external id '1-2333qqdfsdfs'
Project external id '1-dsdfsd34343'

dnoeth 4628 posts Joined 11/04
18 Jan 2016

None of your example rows match this RegEx searching for:
'Order' followed by an optional space or single quote followed by a 10 to 15 digit order number followed by an optional single quote.
Btw, your search is case-insensitive in REGEXP_INSTR, but case-sensitive in REGEXP_REPLACE.


You must sign in to leave a comment.