How to convert string into number in netezza

Question

i have table_1 include some field, one field name is THROUGHPUT in char datatype(the value is number)

i want to use function avg() to get average value from all THROUGHPUT value in netezza,

i have tried code for convert to float :

SELECT Cast(THROUGPUT as float) AS A
FROM   WIFI.WID_NM_DETAIL
ORDER BY A

when i execute the code, i have error ERROR: Bad float8 input format '1,248.2',

i tried to another datatype, but still error

How can i fix it ?

Thx

score 0 · Accepted Answer · edited Dec 12 '19 at 05:54

0

Depending what kind of format mask you use for displaying your throughput and what locale for your database you should either remove all commas or all dots. Assuming that the comma here separates the thousands, you can try one of these

CAST(REPLACE(throughput, '.', '') AS FLOAT)

or

CAST(TRANSLATE(throughput, ',', '') AS FLOAT)

edited Dec 12 '19 at 05:54

Doug_Ivison

778
7
17

answered Sep 26 '14 at 12:33

sqlab

6,412
1
14
29

waw...thx for your answer..but when i tried it, i got error "ERROR: zero-length delimited identifier" :( – ihsansat Sep 29 '14 at 01:33
I found two alternative ways specific to netezza – sqlab Sep 29 '14 at 12:26

How to convert string into number in netezza

1 Answers1