question

user_edit avatar image
user_edit asked

How to remove extra period from a decimal value

Hello, I am working with numbers that are entered incorrectly in the database and I am trying to remove the data error. One of the values entered is like this: "14..89" when it should have been entered like this "14.89". I tried to use the reg_exp functiont o fix it but it is not working, here is what I tried so far:


SELECT REGEXP_REPLACE('14..89', '[^.0-9]+', '') from dual;


SELECT regexp_replace(regexp_substr('14..89', '.*(\..*)$', 1, 1, NULL, 1), '[^.0-9]+', '') FROM dual;


if someone could please help me, that would be great. Thank you

oracle-sql-developer
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Jeff Moden avatar image
Jeff Moden answered

Lordy, that all seems complicated.

I don't know much about Oracle SQL or PL/SQL anymore but, IIRC, there IS a straight forward REPLACE function that doesn't require all the hoops to jump through that RegExp_Replace requires of you.

SELECT REPLACE('14..89','..','.') FROM DUAL
;


10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.