CODEWITHSUNDEEP
sqlsql-server
Roger Steinberg
Roger Steinberg

Reputation: 1604

WHERE condition to exclude amounts with decimals ending with '0's or '5's

Objective:

I have a column 'amount' with decimals. I am trying to exclude rows where the amount value ends either with '0's or '5's.

How can I achieve that...

Column type: decimal (7,2)

Ex: numbers to exclude

10.25
11.20
100.00

Upvotes: 0

Views: 707

Answers (3)

Cato
Cato

Reputation: 3701

What I did here is changed the number into a string, trimmed off the trailing blanks, and then reversed the string to take the first character to see if it was no 1 or 5

SELECT * into #test FROM (SELECT CAST(10.25 as decimal(7,2)) as val UNION SELECT 8.21 UNION SELECT 6.00) DQ 

select * from #test WHERE LEFT(REVERSE(RTRIM(CAST(val as nvarchar(50)))),1) NOT IN ('5', '0')

drop table #test

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270703

An interesting way to do this uses "modular" arithmetic

where col % 0.1 not in (0.00, 0.05)

The % operator works on non-integer bases as well as integer ones.

Upvotes: 3

Hogan
Hogan

Reputation: 70538

You probably want this:

 WHERE (CAST(your_field * 100 AS INTEGER) % 5) <> 0

But it is hard to tell without more detail on your data type. Also there can be funky rounding issues with floating point values.

Upvotes: 3

Related Questions