CODEWITHSUNDEEP
sqldatabasecastingamazon-redshift
dtsui
dtsui

Reputation: 39

Converting Scientific Notation to float (string to float) in SQL (Redshift)

I am trying to convert/cast a string of scientific notation (for example, '9.62809864308e-05') into a float in SQL.

I tried the standard method: CONVERT(FLOAT, x) where x = '9.62809864308e-05', but it returns the error message: Unimplemented fixed char conversion function - bpchar_float8:2585.

What I'm doing is very straightforward. My table has 2 columns: ID and rate (with rate being the string scientific notation that I am trying to cast to float). I added a 3rd column to my table and tried to populate the 3rd column with the float representation of x:

UPDATE my_table
SET 3rd_column = CONVERT(FLOAT, 2nd_column)

Data type of 2nd_column is CHAR(20)

Furthermore, not every string float is in scientific notation -- some are in normal float notation. So I'm wondering if there is a built in function that can take care of all of this.

Thank you!

Upvotes: 0

Views: 4935

Answers (1)

dtsui
dtsui

Reputation: 39

Turns out that for any string representation of a float x, so let's say x = '0.00023' or x = '2.3e-04'

CONVERT(FLOAT, x) will Convert the data type of x from char (string) to float.

The reason why it didn't work for me was my string contained white spaces.

Upvotes: 2

Related Questions