CODEWITHSUNDEEP
javasqlregexoracle-database
user1726550
user1726550

Reputation: 81

How to use Java regular expression in Oracle regexp_replace

How do I use this Java regular expression ("\\d+\\.*\\d+"); in the Oracle regexp_replace function. This is working fine in Java, but with Oracle, it is not working.

Example data:

<Tier><grade><><sdlc><17,10><> : result should be 17.10
<><sdlc><16,909312> : 16.909312
<><sdlc><11396,87> : 11396.87
<20121217> : 20121217
<UNIT><6086> : 6086
<Tier1><><sdlc><0,47> : 0.47

Upvotes: 0

Views: 2333

Answers (3)

Shannon Severance
Shannon Severance

Reputation: 18410

Use replace then regexp_substr followed by translate rather than using regexp_replace:

select translate(regexp_substr(replace(str, ',>', '>')
        , '<[[:digit:]]+(,[[:digit:]]*)?>')
    , ',<>', '.')
from so

sqlfiddle

From Oracle Database SQL Language Reference 11g Release 2 (11.2):

Upvotes: 1

Alan Moore
Alan Moore

Reputation: 75232

Be aware that the Java's regex flavor is not the same as the one used in Oracle's REGEXP_whatever functions. The Java flavor supports a lot more features, but it's susceptible to badly written regexes that can cause extremely poor performance. The syntax can be a lot different, too. In other words, you can't expect a regex to work in Oracle just because it worked in Java. On the plus side, you don't have to use as many backslashes in Oracle.

I'm not fluent in Oracle, but I think this is what you're looking for:

SELECT REGEXP_REPLACE(mycolumn, '([[:digit:]]+),([[:digit:]]+)', '\1.\2', 1, 0, 'c') FROM mytable;

Upvotes: 0

Carlos Vergara
Carlos Vergara

Reputation: 3622

The regular expression in Oracle should be \d+?\.*\d+?. If you want the period just once, if anything, use \d+?\.?\d+? instead. Here you can see more about Oracle regexps.

EDIT: The complete regexp for each line, to just get the number at the end, should be .*?<\d+?,?\d+?>.*?(\d+?\.?\d+?) (I'm grouping what you need at the end of the regexp).

EDIT 2: If for some reason the qualifiers *? and +? don't seem to work, omit the ? from both. I find it extremely weird that Oracle uses that syntax while other languages use plain * and +.

Upvotes: 1

Related Questions