Postgres 13

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select version();


version


PostgreSQL 13.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.4.1 20200928 (Red Hat 8.4.1-1), 64-bit


CREATE TABLE payment
(
  id INT,
  amount TEXT NOT NULL
);
INSERT INTO payment VALUES
(1,  'KES 0.80'),             
(2,  'KES  0.80'),            
(3,  'KES .80'),           
(4,  'xyzKES 0.80'),          
(5,  'KES . 0.80'),           
(6,  'KES 0.80afasf'),          
(7,  'KES 0.80__asdfa   ..'),
(8,  'KES 0.80..asdfasdf'),
(9,  'KES 0.8.0..asdfasdf'),
(10, 'KES 0.8.0    ..asdfasdf...');
10 rows affected

SELECT
  REGEXP_REPLACE(amount, '[^0-9\.]+|\. +|\.0|\.{2,}', '', 'g')
FROM payment;


regexp_replace


0.80

0.80

.80

0.80

0.80

0.80

0.80

0.80

0.8

0.8