All Forums Tools
anishgoyal007 7 posts Joined 02/14
30 Nov 2015
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.

Anish Goyal

b.saipavankumar 12 posts Joined 03/12
30 Nov 2015

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

Thanks,
Sai Pavan Kumar Bhamidipati

anishgoyal007 7 posts Joined 02/14
09 Dec 2015

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?
 

Anish Goyal

You must sign in to leave a comment.