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
Use replace then regexp_substr followed by translate rather than using regexp_replace:
select translate(regexp_substr(replace(str, ',>', '>')
, '<[[:digit:]]+(,[[:digit:]]*)?>')
, ',<>', '.')
from so
From Oracle Database SQL Language Reference 11g Release 2 (11.2):
< and >, and to match <123,> resulting in 123. Any other cases not covered in your example data?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 +.
result should be., to a .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;
("ddddabcadd")is a correct word, or do you mean("1234asdf1234")?