Remove all dots except its last occurrence-CodePudding

I need regex to remove dots from a number, but not the last one.

What I'd like to do:

100.000.10 -> 100000.10

I tried with:

SELECT REGEXP_REPLACE ('100.100.10', '\.(?![^.] $)|[^0-9.]','') FROM dual;

But it return 100.100.10

CodePudding user response：

You do not need (slow) regular expression and can use (much faster) simple string functions:

SELECT REPLACE(SUBSTR(value, 1, INSTR(value, '.', -1) - 1), '.')
       || SUBSTR(value, INSTR(value, '.', -1)) AS updated_value
FROM   table_name;

Which, for the sample data:

CREATE TABLE table_name (value) AS
SELECT '100.000.10' FROM DUAL;

Outputs:

UPDATED_VALUE
100000.10