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

Question

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

Navigation

#1 by (3 votes)
#2 by (-1 votes)

1

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'm formatting the values that are acceptable for float / double format etc.

I have the following line in my query:

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

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

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

sql query postgresql

asked by anonymous 09.05.2018 / 18:28

2 answers

-1

Make a UPDATE in 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 play the float field.

09.05.2018 / 18:33

How can I show certain products by clicking on a link to another page? Create and destroy session

score 3 · Accepted Answer

How about using the translate() function 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%' );

Inquiry:

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

Output:

|    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: link