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

CodePudding user response：

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>

CodePudding user response：

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 .6 .66 .76' FROM dual UNION ALL 
 SELECT '.6' FROM dual 
)
SELECT REGEXP_REPLACE(col,'.(\d)','0.\1') AS new_col
  FROM t