Need Help - SQL script to find 3rd decimal

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 2005 Forums

Transact-SQL (2005)

Need Help - SQL script to find 3rd decimal

Author

Topic

pmosca
Starting Member

3 Posts

Posted - 2010-04-22 : 11:55:16

I am trying to figure out a way to write a SELECT query to see if the third decimal is NOT a zero.

For example, if a column contains a number 34.0340, then I want to list all of these records. If a column contains 34.2300 then I do not want those.

can that be done?

thanks

Pablo Mosca
IT Manager
Trinity Stairs, Inc.

malpashaa
Constraint Violating Yak Guru

264 Posts

Posted - 2010-04-22 : 12:18:50

Try this:


CAST(CAST(34.2309 AS DECIMAL(9, 5)) * 1000 AS INT) % 10

pmosca
Starting Member

3 Posts

Posted - 2010-04-22 : 12:23:10

I am trying to search multiple tables for numbers that contain a third decimal not equal to zero.

the columns are numeric.

For example...

if the column contains

34.0200
1232.0560
234.4400
451232.123

I want to list only records 1232.0560 and 451232.123

is that possible?

Pablo Mosca
IT Manager
Trinity Stairs, Inc.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-04-22 : 12:41:31

where
SUBSTRING(
SUBSTRING(CAST(your_col AS VARCHAR),CHARINDEX('.',CAST(your_col AS VARCHAR))+1,LEN(CAST(your_col AS VARCHAR)))
,3,1) <> '0'

No, you're never too old to Yak'n'Roll if you're too young to die.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-04-22 : 12:48:16

DECLARE @table table (col1 numeric(16,5))

insert into @table
select 34.0200 union all
select 1232.0560 union all
select 234.4400 union all
select 451232.123
select *
from @table
where right(str((col1*1000),50 ),1) <>'0'

Jim

Everyday I learn something that somebody else already knew

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-04-22 : 12:56:08

I like Malpashaa's best

DECLARE @table table (col1 numeric(16,5))

insert into @table
select 34.0200 union all
select 1232.0560 union all
select 234.4400 union all
select 451232.123

select *
from @table

where CAST(CAST(col1 AS DECIMAL(15, 5)) * 1000 AS INT) % 10 <> 0

Everyday I learn something that somebody else already knew

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-04-23 : 03:56:07

quote:
Originally posted by jimf

DECLARE @table table (col1 numeric(16,5))

insert into @table
select 34.0200 union all
select 1232.0560 union all
select 234.4400 union all
select 451232.123
select *
from @table
where right(str((col1*1000),50 ),1) <>'0'

Jim

Everyday I learn something that somebody else already knew

Beware of rounding
This 1232.0508 will be returned by the above query

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-04-23 : 03:57:26

Another method is

select * from @table
where col1*1000%10>=1

Madhivanan

Failing to plan is Planning to fail

pmosca
Starting Member

3 Posts

Posted - 2010-04-23 : 08:38:18

Thank you everyone...

I used webfreds, which worked very nicely for me.

thanks!!

Pablo Mosca
IT Manager
Trinity Stairs, Inc.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-04-23 : 08:40:34

quote:
Originally posted by pmosca

Thank you everyone...

I used webfreds, which worked very nicely for me.

thanks!!

Pablo Mosca
IT Manager
Trinity Stairs, Inc.

Have you seen the previous reply?
Did you get any wrong result with that?

Madhivanan

Failing to plan is Planning to fail

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-04-23 : 08:42:02

welcome

No, you're never too old to Yak'n'Roll if you're too young to die.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-23 : 08:48:57

[code]select * from @table
where FLOOR(col1* 100)!= col1*100[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-04-23 : 08:51:51

quote:
Originally posted by visakh16

select * from @table
where FLOOR(col1* 100)!= col1*100

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

It falis for the value 1232.05080

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-23 : 08:55:21

[code]select * from @table
where col1 NOT LIKE '%.__0%'[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-04-23 : 09:01:09

There are too many solutions. Someone with enogh time should test all of them to know which takes less time

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources