SELECT values with decimal places

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

SELECT values with decimal places

Author

Topic

cornall
Posting Yak Master

148 Posts

Posted - 2008-10-10 : 10:30:05

Hi,

If I have a table

Value
100
200
459.99
2342
12121.192
23
23.89

How do I SELECT only the values with a decimal place?

Even better how do I UPDATE the recors to lose the decimal place without rounding the numbers up or down?

Cheers Dave

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-10-10 : 10:34:16

What's the datatype of the column?

you could do


SELECT * FROM <yourtable> WHERE CONVERT(VARCHAR(255), <yourcolumn>) LIKE '%.%'

Performance will.... suck but it would work.

-------------
Charlie

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-10-10 : 10:37:02

You can loose the decimal places by casting to int

SELECT CAST(<yourColumn> AS INT) FROM <yourtable>

-------------
Charlie

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-10-10 : 10:38:31

for selecting -- quicker would be to use modulus (%)


SELECT * FROM <yourTable> WHERE <youCol> % 1 <> 0

-------------
Charlie

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-10-10 : 10:46:05

Transact Charlie, your methods would fail if there is data like 100.0

Madhivanan

Failing to plan is Planning to fail

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-10-10 : 10:55:33

true -- yes I hadn't thought of that.

In my defence there's nothing like that in the sample data.

Presumably a cast to INT would still remove the . and likewise a LIKE comparison would still find it. Only the modulus wouldn't work?

-------------
Charlie

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-10-10 : 11:13:30

quote:
Originally posted by Transact Charlie

true -- yes I hadn't thought of that.

In my defence there's nothing like that in the sample data.

Presumably a cast to INT would still remove the . and likewise a LIKE comparison would still find it. Only the modulus wouldn't work?

-------------
Charlie

Your first method would work

Madhivanan

Failing to plan is Planning to fail

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-10-10 : 11:21:32

madhivanan (as always) is right. And I'm not :(

to drop the . point if the data is of a character type (which it looks like it is) you can't just CAST to INT directly.

You can do this though.....


SELECT CAST(CAST([value] AS DECIMAL(x,y)) AS INT) FROM <yourTable>

Make sure that you give the decimal declaration enough scale (x) and precision (y) to deal with the widest value in your data.

Thanks for the correction madhivanan.

-------------
Charlie

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-10-14 : 03:02:05

<<
Thanks for the correction madhivanan.
>>

You are welcome