×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• 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.

# 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.

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:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!