Converting to number from string with special characters

Question

I have a input column with below values.

1.2%
111.00$
$100.00
aa
ss

Output expected

1.2
111.00
100.00
null
null

I am trying to use REGEXP_REPLACE and tried replacing every character that is not a digit or "." so that 1.2% will become 1.2. Here is the query I tried but this didn't work.

regexp_replace('%1.aa2', '[^[\d|\.]]', '')

Can anyone suggest how to do that? and what I am doing wrong? I am working on Oracle 11.2 database with pl/sql developer.

this didnt worked, it replaces everything except ".". Here is the query I used select regexp_replace('%1.aa2', '[^\d.]+', '') from dual — Nitesh, Oct 30 '15 at 12:09
I guess you'll have to escape the backslash too in that case. Try `\\d` instead of `\d`. — hjpotter92, Oct 30 '15 at 12:10

score 1 · Answer 1 · answered Oct 30 '15 at 11:58

1

Use POSIX class for matching digit chars ie [:digit:]. So this negated class [^[:digit:].] should match any character but not of dot or digit.

regexp_replace('%1.aa2', '[^[:digit:].]', '')

answered Oct 30 '15 at 11:58

Avinash Raj

172,303
28
230
274

how is this different from '[^[\d.]] ? – Nitesh Oct 30 '15 at 12:13
don't know whether oracle supports `\d` . If it supports, then it would be `[^\d.]` – Avinash Raj Oct 30 '15 at 12:14
Thanks @Avinash raj. [:digit] worked fine. I am sure that oracle supports '\d' as well. Check this statement select regexp_replace('asas12313', '\d', '-') from dual – Nitesh Oct 30 '15 at 14:15

score 1 · Answer 2 · answered Oct 30 '15 at 12:26

You can use the [^0-9.] regex and replace with empty string:

with testdata(txt) as (
  select 'ss' from dual
  union
  select 'aa' from dual
  union
  select '$100.00'      from dual
  union
  select '111.00$'             from dual
  union
  select '1.2%'             from dual
)
select regexp_replace(txt, '[^0-9.]', '')
from testdata;

Result of an SQL fiddle:

Converting to number from string with special characters

2 Answers2