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
(
  amount TEXT NOT NULL
);
INSERT INTO payment VALUES
('KES 0.80'),             
('KES  0.80'),            
('KES   0.80'),           
('xyzKES 0.80'),          
('KES . 0.80'),           
('KES 0.80afasf'),          
('KES 0.80__asdfa'),
('KES 0.80..asdfasdf'),
('KES 0.8.0..asdfasdf'),
('KES 0.8.0..asdfasdf');
10 rows affected

SELECT 
  SUBSTRING(amount FROM '([0-9\.]+)*')
FROM payment;


substring


null

null

null

null

null

null

null

null

null

null


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


regexp_replace


0.80

0.80

0.80

0.80

.0.80

0.80

0.80

0.80..

0.8.0..

0.8.0..


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


btrim


0.80

0.80

0.80

0.80

0.80

0.80

0.80

0.80

0.8.0

0.8.0


SELECT 
  REVERSE(BTRIM(REGEXP_REPLACE(amount, '[^0-9\.]+', '', 'g'), '.'))
FROM payment;


reverse


08.0

08.0

08.0

08.0

08.0

08.0

08.0

08.0

0.8.0

0.8.0


SELECT
ARRAY_LENGTH
(
  STRING_TO_ARRAY
  (
    REVERSE(BTRIM(REGEXP_REPLACE(amount, '[^0-9\.]+', '', 'g'), '.'))
  , '.'
  )
, 1
)
FROM payment;


array_length


2

2

2

2

2

2

2

2

3

3


SELECT
  CASE
    WHEN
ARRAY_LENGTH
(
  STRING_TO_ARRAY
  (
    REVERSE(BTRIM(REGEXP_REPLACE(amount, '[^0-9\.]+', '', 'g'), '.'))
  , '.'
  )
, 1
) > 2 THEN '55555'      -- <<<--- '55555' is just a placeholder!
    ELSE  
    
   REVERSE(ARRAY_TO_STRING(  STRING_TO_ARRAY
  (
    REVERSE(BTRIM(REGEXP_REPLACE(amount, '[^0-9\.]+', '', 'g'), '.'))
  , '.'
  ), '.' ))
    
   
  END AS str
FROM payment;


str


0.80

0.80

0.80

0.80

0.80

0.80

0.80

0.80

55555

55555


SELECT
  CASE
    WHEN
      ARRAY_LENGTH
      (
        STRING_TO_ARRAY
        (
          REVERSE(BTRIM(REGEXP_REPLACE(amount, '[^0-9\.]+', '', 'g'), '.'))
        , '.'
        )
      , 1
      ) > 2 THEN 

    REVERSE(REGEXP_REPLACE(
    REVERSE(BTRIM(REGEXP_REPLACE(amount, '[^0-9\.]+', '', 'g'), '.')), '\.', ''))


    ELSE  
    
      BTRIM(REGEXP_REPLACE(amount, '[^0-9\.]+', '', 'g'), '.')

   
  END AS str
FROM payment;


str


0.80

0.80

0.80

0.80

0.80

0.80

0.80

0.80

0.80

0.80


SELECT
  CASE
    WHEN
      ARRAY_LENGTH
      (
        STRING_TO_ARRAY
        (
          REVERSE(BTRIM(REGEXP_REPLACE(amount, '[^0-9\.]+', '', 'g'), '.'))
        , '.'
        )
      , 1
      ) > 2 
    THEN 
      REVERSE
      (
        REGEXP_REPLACE
        (
          REVERSE
          (
            BTRIM
            (
              REGEXP_REPLACE(amount, '[^0-9\.]+', '', 'g'), '.')
            ), 
        '\.', ''
        )
      )::DOUBLE PRECISION
    ELSE  
      BTRIM(REGEXP_REPLACE(amount, '[^0-9\.]+', '', 'g'), '.')::DOUBLE PRECISION
  END AS str
FROM payment;


str


0.8

0.8

0.8

0.8

0.8

0.8

0.8

0.8

0.8

0.8
substring
null
null
null
null
null
null
null
null
null
null