Can REGEXP_REPLACE be used to replace multiple strings with multiple replace strings

Database
Enthusiast

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 ?

Tags (1)
6 REPLIES
Junior Contributor

Re: Can REGEXP_REPLACE be used to replace multiple strings with multiple replace strings

You don't need REGEXP_REPLACE in this case, to replace single characters there's

oTRANSLATE('ABC', 'AB', 'ZY')

It's case specific, so for insensitive replacement you must add both upper and lower case:

oTRANSLATE('ABC', 'abAB', 'ZYZY')

Enthusiast

Re: Can REGEXP_REPLACE be used to replace multiple strings with multiple replace strings

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

Teradata Employee

Re: Can REGEXP_REPLACE be used to replace multiple strings with multiple replace strings

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.

Enthusiast

Re: Can REGEXP_REPLACE be used to replace multiple strings with multiple replace strings

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

regexp_replace(source_string,('@databasename','@tablename'),('sandpitdb','employee'),1,0,'i');

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

Re: Can REGEXP_REPLACE be used to replace multiple strings with multiple replace strings

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 :

SELECT

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'

Column1

--------

Project external id '1-156434343xxx'

Project external id '1-2333qqdfsdfs'

Project external id '1-dsdfsd34343'

/

Junior Contributor

Re: Can REGEXP_REPLACE be used to replace multiple strings with multiple replace strings

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.