Finding number of decimal values!!!

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

SQL Server Development (2000)

Finding number of decimal values!!!

Author

Topic

Nitu
Yak Posting Veteran

81 Posts

Posted - 2006-03-17 : 09:23:01

Hi,

I need to write a query which finds records which has a field with 6 decimal values only.

Is there a way to find number of values after '.' in a decimal value.

hope somebody can help me with this,

Thanks in advance,
Nitu

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-03-17 : 09:32:08

Why do you need this?

Take this an example


select * from
(	
	select '12.743535' as data union all
	select '567.345' union all
	select '35.340923455' 
) T where len(substring(data,charindex('.',data)+1,len(data)))=6

Madhivanan

Failing to plan is Planning to fail

Nitu
Yak Posting Veteran

81 Posts

Posted - 2006-03-17 : 09:39:49

quote:
Originally posted by madhivanan

Why do you need this?

I need this because, i want to give a report with records which fail this condition of 6 decimals.

The condition goes like this: Only records with certain criteria can have fields with 6 decimal places, the rest of the records should not have 6 decimal but they should have only two.

select * from
(
select '12.743535' as data union all
select '567.345' union all
select '35.340923455'
) T where len(substring(data,charindex('.',data)+1,len(data)))=6

Thanks for the example.
Madhivanan

Failing to plan is Planning to fail

Nitu
Yak Posting Veteran

81 Posts

Posted - 2006-03-17 : 09:46:35

Hi madhivanan,

I tried you example. The decimal i have is not a string so i cannot perform "len(substring(data,charindex('.',data)+1,len(data)))=6"
on a decimal.

how can we convert a decimal value to a string.

Thanks in advance,
Nitu

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-17 : 09:50:12

"The decimal i have is not a string"
then just convert it to the number of decimal that you required
For example, to convert to 6 decimals

convert(decimal(10,6), your_column)

"how can we convert a decimal value to a string."
select convert(varchar(20), your_decimal_column)

KH
Choice is an illusion, created between those with power, and those without.
Concordantly, while your first question may be the most pertinent, you may or may not realize it is also the most irrelevant

Nitu
Yak Posting Veteran

81 Posts

Posted - 2006-03-17 : 09:56:00

Thanks KH.

I used convert(varchar(20), my_int_column) before many times, but as u see i used it for int. Dint know it works fine for decimals also.

Thanks a lot,
Nitu

CLages
Posting Yak Master

116 Posts

Posted - 2006-03-17 : 11:05:38

try something like this

lages

declare @decimal decimal(12,9)
set @decimal = 123.123456789

SELECT (convert(int ,round(@decimal, 0, 1)) - @decimal) * -1
where
(convert(int ,round(@decimal, 0, 1)) - @decimal) * -1
< 0.999999

Adjust 0,999999 for your needs

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-17 : 20:29:40

quote:
Originally posted by Nitu

Thanks KH.

I used convert(varchar(20), my_int_column) before many times, but as u see i used it for int. Dint know it works fine for decimals also.

Thanks a lot,
Nitu

if you are working with variable, just assign it to a variable with your requied decimal places and it will be converted implicitly

declare
    @var decimal(20,6)
select @var = yourcolumn

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources