CODEWITHSUNDEEP
sqltextcastingamazon-redshiftnumeric
sweti
sweti

Reputation: 15

Text to Number (Redshift)

The field that I am using comes in as type: text and I want to convert it to a number with 2 decimal places. For some reason the following convert it to a number but also rounds it:

"UNIT_PRICE"::numeric

"UNIT_PRICE"::decimal

both convert a 0.65000000000000 text value to 1 but I would like to get a numeric 0.65

enter image description here

Is it possible?

Upvotes: 0

Views: 5470

Answers (3)

Sidhant Gupta
Sidhant Gupta

Reputation: 149

using trunc function:

select trunc("UNIT_PRICE"::numeric,2) ;

Upvotes: 0

ArchAngelPwn
ArchAngelPwn

Reputation: 3046

You can write a cast function with as a decimal to get only 2 decimals back.

SELECT CAST(0.65000000000000 AS DECIMAL(10, 2))

More information about casting as decimal can be found here: SQL Server CAST() Function

Upvotes: 1

Bill Weiner
Bill Weiner

Reputation: 11032

In Redshift the default precision and scale are 18 and 0 - DECIMAL(18,0). This is whole numbers. If you want a different scale you need to specify it. DECIMAL(18,2).

See: Numeric types - Amazon Redshift

Upvotes: 1

Related Questions