CODEWITHSUNDEEP
oracle-databaseoracle11g
user761758
user761758

Reputation: 585

Remove a decimal from many fields

Need to remove a dot from fields within a column (varchar2 datatype). The decimal is not always there, but when it is, it is always in the fifth position, and there are always two zeros behind it. (12E4.00) We need to retain the zeros.

It seems a reasonable use of REGEXP_REPLACE, but wonder if there is a purely sql way of getting it done as well. Under 100,000 rows and 28,000 instances of fields with a decimal embedded.

Appreciate all help flowing.

Upvotes: 0

Views: 1305

Answers (1)

Justin Cave
Justin Cave

Reputation: 231761

It sounds like you just need a simple REPLACE

SQL> with x as (
  2    select '123E4.00' str from dual
  3    union all
  4    select '123K5.00' from dual
  5    union all
  6    select '123K123' from dual
  7  )
  8  select replace( str, '.' )
  9    from x;

REPLACE(
--------
123E400
123K500
123K123

You'd need to turn that into an UPDATE statement against your table

UPDATE some_table
   SET some_column = REPLACE( some_column, '.' )
 WHERE some_column != REPLACE( some_column, '.' )

Upvotes: 1

Related Questions