CODEWITHSUNDEEP
sqlhivehiveqlsqldatatypes
Helpmedaddy
Helpmedaddy

Reputation: 17

how to make a column not to display 0 as decimal but should display decimals other then zero

consider a column which contains both decimal and non decimal number

 col1 
    98
    99.0
    66.2 
    99.6
    76

Expected after cast function

 col1 
    98
    99
    66.2 
    99.6
    76

I tried to do it as

select cast(col1 as decimal)

Upvotes: 1

Views: 1382

Answers (4)

leftjoin
leftjoin

Reputation: 38335

Use regexp_replace(col1,'\\.0+$','') --matches . literally and any number of zeroes at the end of the string.

Demo:

with mytable as (
select  
stack(5,'98',
        '99.0',
        '66.2',
        '99.6',
        '76') as col1 
)

select regexp_replace(col1,'\\.0+$','') as col1 from mytable;

Result:

col1
98
99
66.2
99.6
76

But it will leave values like 66.20 as is. If you want to remove also non-significant trailing zeroes: 66.20 --> 66.2 and 66.60200 --> 66.602, 66.0 --> 66 and leave 600 as is then use this:

regexp_replace(regexp_replace(col1,'\\.0+$',''),'(\\d+\\.\\d+?)0+$','$1')

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269953

You need to convert to a string, if you want to control the formatting. This would often be done in the application level. But you can do it in the database as well:

select replace(printf(col1, '%.1f', col1), '.0', '')

This formats the string using two decimal places. It then removes '.0' if present.

Upvotes: 0

dbdipak
dbdipak

Reputation: 21

Have you tried float data type ?

select cast(99.0 as float)

Upvotes: 0

mck
mck

Reputation: 42352

You can use case when to do this. The resulting column will be of string type though because of the display format that you prefer.

select 
    case when round(col1, 0) = col1 
         then cast(cast(round(col1, 0) as int) as string)
         else cast(col1 as string)
    end
from mytable

Upvotes: 0

Related Questions