Regex function REGEXP_REPLACE not accepting parameter

Tools
Enthusiast

Regex function REGEXP_REPLACE not accepting parameter

Hi All,

I want to replace all occurences of RAM with SHYAM in a column. I used for query:

UPDATE  table_name

SET col1=REGEXP_REPLACE(col1,RAM,SHYAM,1,0,'i');

Getting error: 9134, Occurences should be greater than 0. I tried on DEV box, it is working fine. However on PROD box, this is failing.

Please help.

2 REPLIES
Enthusiast

Re: Regex function REGEXP_REPLACE not accepting parameter

Anish,

Ideally it should work in prod box as well. By the looks of it, it seems quotes are missing in the query for RAM & SHYAM

Enthusiast

Re: Regex function REGEXP_REPLACE not accepting parameter

I tried using single quotes, same results, it is not working on PROD

UPDATE  table_name

SET col1=REGEXP_REPLACE(col1,'RAM','SHYAM',1,0,'i');

Alternative, i have to go for following (assuming max 4 instances can be there):

UPDATE  table_name

SET col1=REGEXP_REPLACE(col1,'RAM','SHYAM',1,1,'i');

UPDATE  table_name

SET col1=REGEXP_REPLACE(col1,'RAM','SHYAM',1,2,'i');

UPDATE  table_name

SET col1=REGEXP_REPLACE(col1,'RAM','SHYAM',1,3,'i');

UPDATE  table_name

SET col1=REGEXP_REPLACE(col1,'RAM','SHYAM',1,4,'i');

Does PROD server missing any patch?