×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

How to find any number having all the four decimal places

How to find any number having all the four decimal places

How to find any number having all the four decimal places

(OP)
hi,

I am facing a difficulty in finding the whether the number is in four decimal accuracy has all the values for all the four decimals instead of zeros in a huge set of data.

Example:
i have a number, 6.0000
and the other number 16.5600
and the other number 15.6754

I am interested to find out,  do we have any numbers having values for all the four decimal places(3rd option).

I am executing this in Sybase. But if you could give me the SQL query, i can tweak it and use it for Sybase. Or if you give me Sybase query that is also OK.

Thanks,
VIJ

RE: How to find any number having all the four decimal places

Basically, you want to see if the 4th position is 0 or not.  To do this, I would multiply by 10000 and then use the mod operator 10.  Like this:

CODE

Declare @Temp table(data Decimal(10,4))

insert into @Temp Values(6)
insert into @Temp Values(16.56)
insert into @Temp Values(15.6754)

Select Data * 10000 % 10 From @Temp

If you run this on SQL2000, you get an error because you need to cast the first calculation to an integer first, like this:

CODE


Declare @Temp table(data Decimal(10,4))

insert into @Temp Values(6)
insert into @Temp Values(16.56)
insert into @Temp Values(15.6754)

Select Cast(Data * 10000 as int) % 10 From @Temp

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

RE: How to find any number having all the four decimal places

gmmastros, I think the OP wants to determine if any digit of the precision has a non-zero value.  You may know of an easier (aka more efficient) way, but off the top of my head, they could convert to char and check:

CODE

declare @num decimal(10,4)
set @num = 15.6714

declare @charNumber char(4)
select @charNumber = convert(varchar, (@num-convert(int, @num)) * 10000)

select    case when (
    case when substring(@charNumber,1,1) = 0 then 0 else 1 end
+    case when substring(@charNumber,2,1) = 0 then 0 else 1 end
+    case when substring(@charNumber,3,1) = 0 then 0 else 1 end
+    case when substring(@charNumber,4,1) = 0 then 0 else 1 end
) = 4 then 'No zeros' else ' zero embedded' end
   


     Mark

"You guys pair up in groups of three, then line up in a circle."
- Bill Peterson, a Florida State football coach

RE: How to find any number having all the four decimal places

(OP)
Thanks very bunch. It took a while for me to implement the same in Sybase. Finally it worked out.

Best Regards,
VIJ

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close