CODEWITHSUNDEEP
sqlsql-server-2008
ProdSupport
ProdSupport

Reputation: 81

How to identify records with only Zeros after decimal point?

Please refer to the screenshot:

SS

How would you write a case statement to identify records with only zeros after the decimal point.

This is what the structure of my select case statement looks like:

SELECT CASE WHEN (ONLY ZEROS AFTER 
DECIMAL POINT)
THEN CAST(VALUE AS int) FROM MyTable

I'm keeping is as varchar(25) only because there are two more fields that I need to combine with the above numbers.

Upvotes: 0

Views: 303

Answers (2)

Igor
Igor

Reputation: 62258

You could do something similar to Gordon's answer but this takes into account that you are storing the number as varchar(25)

declare @mytable table (value varchar(25) not null)
insert into @mytable (value) values ('1.31'),('5.00'),('2.500'),('6.00'),('8.0000'),('1.0'),('3.0000000')

select coalesce(cast((case when DecimalValue = floor(DecimalValue) then cast(DecimalValue as int) end) as varchar(25)), VarcharValue)
from (select cast(value as decimal(18,9)) as DecimalValue, value as VarcharValue from @mytable) as tmp

http://rextester.com/QRRW4456

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270713

One method is:

(case when value = floor(value) then cast(value as int) end)

Upvotes: 3

Related Questions