how to escape brackets in regex replace in oracle sql

Question

My query is not working as expected :

SELECT REGEXP_REPLACE('This is testing data SIL(TM)T for once ', 
'SIL(TM)T', 'SIL<REFERENCE ID="8208" TYPE="trademark"/>T')as 
Newdescriptiontext from dual

output should be:

This is testing data SIL<REFERENCE ID="8208" TYPE="trademark"/>T for once

which is not the case .Need guidance .

schurik · Accepted Answer · 2017-05-30 14:21:41Z

1

try replace instead of regexp_replace

SELECT REPLACE('This is testing data SIL(TM)T for once ', 
'SIL(TM)T', 'SIL<REFERENCE ID="8208" TYPE="trademark"/>T')as 
Newdescriptiontext from dual;

answered May 30, 2017 at 14:21

schurik

7,9582 gold badges26 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Animesh Sheolikar Over a year ago

Hi @schurik it is working but can you tell why it was not in regexp_replace.

Aleksej · Accepted Answer · 2017-05-30 14:58:39Z

1

You simply have to escape the parentheses:

SELECT REGEXP_REPLACE('This is testing data SIL(TM)T for once ', 
                       'SIL\(TM\)T', 'SIL<REFERENCE ID="8208" TYPE="trademark"/>T')as Newdescriptiontext
from dual

In a regexp, they are used to delimit a "subexpression", so '(TM)' matches 'TM'; if you escape them, they'll be interpreted as plain characters, thus having '\(TM\)' matching '(TM)'

edited May 30, 2017 at 14:58

answered May 30, 2017 at 14:29

Aleksej

23.1k6 gold badges38 silver badges41 bronze badges

Collectives™ on Stack Overflow

how to escape brackets in regex replace in oracle sql

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related