Convert from varchar to float when the string is empty and/or blank

Question

I have a column where the strings are in the following format: "12.05%"

I need to do some calculations with these values, and I am formatting the values that are acceptable for the float/double format etc.

I have the following line in my query:

cast(replace(replace(replace(campo, '%', ''), '.', ''), ',', '.') as float)

It runs as expected without problems, but this column has null values or no value, in this case have no number so that can be converted to float and this ends up generating an error, I need to treat these values to receive "0".

ERROR:  invalid input syntax for type double precision: ""

Answer 1

How about using the function translate() combined with nullif() and coalesce():

translate( coalesce( nullif(trim(campo),''), '0,0%') , ',%', '.' )::NUMERIC

For example:

CREATE TABLE tb_foobar
(
  id BIGINT PRIMARY KEY,
  campo TEXT
);


INSERT INTO tb_foobar ( id, campo ) VALUES ( 1, NULL ); 
INSERT INTO tb_foobar ( id, campo ) VALUES ( 2, '' );   
INSERT INTO tb_foobar ( id, campo ) VALUES ( 3, '   ' );  
INSERT INTO tb_foobar ( id, campo ) VALUES ( 4, '12,25%' );
INSERT INTO tb_foobar ( id, campo ) VALUES ( 5, '0,00%' );
INSERT INTO tb_foobar ( id, campo ) VALUES ( 6, '33,33%' );
INSERT INTO tb_foobar ( id, campo ) VALUES ( 7, '233,557%' );
INSERT INTO tb_foobar ( id, campo ) VALUES ( 8, '12.25%' );
INSERT INTO tb_foobar ( id, campo ) VALUES ( 9, '10.50%' );
INSERT INTO tb_foobar ( id, campo ) VALUES ( 10, '33%' );

Consultation:

SELECT
  campo,
  translate( coalesce( nullif(trim(campo),''), '0,0%') , ',%', '.' )::NUMERIC
FROM
  tb_foobar;

Exit:

|    campo | translate |
|----------|-----------|
|   (null) |         0 |
|          |         0 |
|          |         0 |
|   12,25% |     12.25 |
|    0,00% |         0 |
|   33,33% |     33.33 |
| 233,557% |   233.557 |
|   12.25% |     12.25 |
|   10.50% |      10.5 |
|      33% |        33 |

Sqlfiddle: http://sqlfiddle.com/#! 17/406fd/2

Answer 2

Make a UPDATE on your table

UPDATE sua_tabela 
SET campo = '0%' 
WHERE campo = '' OR campo IS NULL

Then you will have all the fields in the required pattern for your cast and then throw the field to float.