How to REGEXP_REPLACE special character

2.3k views Asked by At

I am having issue with following regex

select REGEXP_REPLACE(declinereasondesc, '(.+)(£)(\d+)', '\1\3 (GBP)') as r from DECLINEREASON t

it does not match following rows

Too expensive : By less than £100
Too expensive : By more than £200

Expected outcome

Too expensive : By less than 100 (GBP)
Too expensive : By more than 200 (GBP)

EDIT:

screenshot for non-believers
enter image description here

1

There are 1 answers

6
Matas Vaitkevicius On BEST ANSWER

Figured it out myself problem is £ as I am sure everyone suspected

Solution contains two steps first is to get symbol code, even if you copy paste £ into select ascii() from dual it does not fly. You have to select the symbol like following to get correct code.

select ascii(substr(declinereasondesc, 30,1)) from DECLINEREASON t
where declinereasonid = 7;

In my case it gave 49827

then

select REGEXP_REPLACE(declinereasondesc, '(.+)('||chr(49827)||')(\d+)', '\1\3 (GBP)') from DECLINEREASON t;

and only then it works. enter image description here