0

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

Matas Vaitkevicius
  • 58,075
  • 31
  • 238
  • 265

1 Answers1

0

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

Matas Vaitkevicius
  • 58,075
  • 31
  • 238
  • 265
  • What is the oracle character set in your database?I think it is due to NLS_CHARACTERSET parameter in your DB – arunb2w Nov 19 '14 at 12:06
  • @arunb2w Could be `SELECT value$ FROM sys.props$ WHERE name = 'NLS_CHARACTERSET' ;` gives me `ORA-00942: table or view does not exist `. So can't confirm. – Matas Vaitkevicius Nov 19 '14 at 12:13
  • Can you try this **select * from v$nls_parameters where parameter in ('NLS_CHARACTERSET','NLS_LANGUAGE');** – arunb2w Nov 19 '14 at 12:15
  • I am happy that you solved your problem. But ascii value of £ is 163. I would suggest you to change your character set to avoid these kind of problems in future. ASCII reference :: http://www.ascii-code.com/ – arunb2w Nov 19 '14 at 12:23
  • @arunb2w If only I could... :) – Matas Vaitkevicius Nov 19 '14 at 12:29
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/65208/discussion-between-arunb2w-and-liufa). – arunb2w Nov 19 '14 at 12:30