How to find any number having all the four decimal places

[VIJSQL01]

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

 

[gmmastros]

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:

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:

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

 

[MarkSweetland]

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:

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

 

[VIJSQL01]

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

Best Regards,
VIJ