how to update values from .6 to 0.6 for all records in a table

Question

In my case I have to update all the records which starts with point to zero point eg: .6 to 0.6 in one go can it be done using regex in plsql

Answer 1

Could you post some examples? For such a sample data set, it kind of works ...

SQL> WITH
  2     test (col)
  3     AS
  4        (SELECT 'Your total value is .6' FROM DUAL
  5         UNION ALL
  6         SELECT 'This costs .25 USD' FROM DUAL
  7         UNION ALL
  8         SELECT 'What about 23.5?' FROM DUAL
  9         UNION ALL
 10         SELECT 'Total sales are .7 and .5' FROM DUAL
 11         UNION ALL
 12         SELECT 'Totals are 0.2 and .34 thankyou.' FROM DUAL)
 13  SELECT col, REGEXP_REPLACE (col, ' \.', ' 0.') result
 14    FROM test;

COL                              RESULT
-------------------------------- ----------------------------------------
Your total value is .6           Your total value is 0.6
This costs .25 USD               This costs 0.25 USD
What about 23.5?                 What about 23.5?
Total sales are .7 and .5        Total sales are 0.7 and 0.5
Totals are 0.2 and .34 thankyou. Totals are 0.2 and 0.34 thankyou.

SQL>

Answer 2

You can use REGEXP_REPLACE() with '([.\d])' pattern to be replace with '0.' such as

WITH t(col) AS
(
 SELECT 'your total value is .. .6 .66 .76'     FROM dual UNION ALL
 SELECT 'your total value is .. 0.6 .16  0.761' FROM dual UNION ALL
 SELECT 'your total value is .6 .66 .76'        FROM dual UNION ALL 
 SELECT '.6'                                    FROM dual 
)
SELECT REPLACE(REGEXP_REPLACE(col,'\.(\d)','0.\1'),'00.','0.') AS new_col
  FROM t

NEW_COL
your total value is .. 0.6 0.66 0.76
your total value is .. 0.6 0.16 0.761
your total value is 0.6 0.66 0.76
0.6

where an extra REPLACE() function is used considering the cases some decimal numbers with 0. might already exist

Demo