How to determine the number of decimal places in a number?

  • Follow


Hi,

Is there an idiomatic way of determining the number of decimal places
in a number (i.e. 0.123 = 3, 0.123456 = 6 etc.)?
Other than converting the number to a string and using string
functions to pull the characters after the decimal place?

Thanks,
Frank.
0
Reply Frank 12/17/2009 12:46:06 PM

Frank
declare @v sql_variant

set @v=0.123111

select SQL_VARIANT_PROPERTY(@v, 'Precision') as BaseType





"Frank" <francis.moore@gmail.com> wrote in message 
news:0b65f019-7eb7-412d-9318-a7c82e388ea3@m16g2000yqc.googlegroups.com...
> Hi,
>
> Is there an idiomatic way of determining the number of decimal places
> in a number (i.e. 0.123 = 3, 0.123456 = 6 etc.)?
> Other than converting the number to a string and using string
> functions to pull the characters after the decimal place?
>
> Thanks,
> Frank. 


0
Reply Uri 12/17/2009 1:03:17 PM

Hi Uri,

Thanks for the response.
However, apologies, my example wasn't quite correct.
As well as 0.123 = 3 and 0.123456 = 6, I also want 0.123000 to return
3, i.e. chop off the last 3 zeroes.
Using NUMERIC(28,6) as we do to hold the decimal value pads the
decimal portion with zeroes.
Is there a way to truncate the zeroes and then use the
SQL_VARIANT_PROPERTY on the result?

Thanks,
Frank.
0
Reply Frank 12/17/2009 1:14:49 PM

Frank
On the client
@v= '1.5500';
       Response.Write(Convert.ToDouble(@v));

and then run SQL_VARIANT_PROPERTY


"Frank" <francis.moore@gmail.com> wrote in message 
news:f788f4c9-c303-4724-ac81-65c06762df98@p8g2000yqb.googlegroups.com...
> Hi Uri,
>
> Thanks for the response.
> However, apologies, my example wasn't quite correct.
> As well as 0.123 = 3 and 0.123456 = 6, I also want 0.123000 to return
> 3, i.e. chop off the last 3 zeroes.
> Using NUMERIC(28,6) as we do to hold the decimal value pads the
> decimal portion with zeroes.
> Is there a way to truncate the zeroes and then use the
> SQL_VARIANT_PROPERTY on the result?
>
> Thanks,
> Frank. 


0
Reply Uri 12/17/2009 1:39:32 PM

Frank wrote:
> However, apologies, my example wasn't quite correct.
> As well as 0.123 = 3 and 0.123456 = 6, I also want 0.123000 to return
> 3, i.e. chop off the last 3 zeroes.
> Using NUMERIC(28,6) as we do to hold the decimal value pads the
> decimal portion with zeroes.

I think you'd have to record the number of decimal places when you put the 
number in, because 0.123 means something different from 0.123000: 0.123 
would be 0.123+/-0.0005 and 0.123000 means 0.123000+/-0.0000005.

Andrew 


0
Reply Andrew 12/17/2009 1:51:30 PM

Hi Uri,

The code to do the check needs to be in the backend, in the SQL Server
database, so there is no access to the .NET conversion routine that
you mentioned (unless I'm missing something). However, that gave me
the idea to use a float conversion to strip the padded zeroes.
The following snippet of code appears to work (unless someone tells me
different). The results are after the comments:

DECLARE @n NUMERIC(28,6)
		, @f FLOAT(6)
		, @v VARCHAR(28);
SET @n = 0.123000;
SET @f = CONVERT(float(6), @n);
SET @v = CONVERT(varchar(28), @f);
SELECT
@n;
-- 0.123000
SELECT
@f;
-- 0.123
SELECT
@v;
-- 0.123
SELECT LEN(SUBSTRING(@v, CHARINDEX('.', @v, 0) + 1, LEN(@v))); -- 3

Thanks for your help.

Andrew,

Thanks for your response as well.
It's the precision that people are primarily interested in.
Once we have the number of digits, we will still be using the original
value.

Many thanks,
Frank.
0
Reply Frank 12/17/2009 2:48:49 PM

SQL Server returns data - not formatted text. A certain value for a given 
data type is internally represented as some binary value. We can only talk 
about trailing zeroes when the data has been delivered to the client 
application and that data is presented to us humans as something 
human-readable. As an example, say the type for the data is decimal(9,4), 
and we have below two values:

123.34
123.3400

Both are exactly the same and will be internally represented as the same 
value. The bit-pattern that represent this value for the client application 
do not carry any trailing spaces - it is not part of the representation. 
I.e., either convert to string (not recommended) or do the formatting at the 
client level. Another option would be to conditionally return different 
types (depending on how many decimals), but that would be a nightmare!

-- 
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi



"Frank" <francis.moore@gmail.com> wrote in message 
news:f788f4c9-c303-4724-ac81-65c06762df98@p8g2000yqb.googlegroups.com...
> Hi Uri,
>
> Thanks for the response.
> However, apologies, my example wasn't quite correct.
> As well as 0.123 = 3 and 0.123456 = 6, I also want 0.123000 to return
> 3, i.e. chop off the last 3 zeroes.
> Using NUMERIC(28,6) as we do to hold the decimal value pads the
> decimal portion with zeroes.
> Is there a way to truncate the zeroes and then use the
> SQL_VARIANT_PROPERTY on the result?
>
> Thanks,
> Frank. 

0
Reply Tibor 12/17/2009 4:16:54 PM

Try this:

DECLARE @x DECIMAL(38, 18);

SET @x = 12.120340100;

SELECT LEN(CAST(REVERSE(STUFF(CAST(@x % 1 AS VARCHAR(38)), 1, 2, '')) AS DECIMAL(38, 0)));

-----------
7

-- 
Plamen Ratchev
http://www.SQLStudio.com
0
Reply Plamen 12/17/2009 4:26:42 PM

Try this:

DECLARE @n NUMERIC(38, 18), @f FLOAT(38), @v VARCHAR(38);
SET @n = 0.100001000001;
SET @f = CONVERT(FLOAT(38), @n);
SET @v = CONVERT(VARCHAR(38), @f);
SELECT @n;
-- 0.100001000001000000
SELECT @f;
-- 0.100001000001
SELECT @v;
-- 0.100001
SELECT LEN(SUBSTRING(@v, CHARINDEX('.', @v, 0) + 1, LEN(@v))); -- 6

-- 
Plamen Ratchev
http://www.SQLStudio.com
0
Reply Plamen 12/17/2009 4:34:12 PM

Hi Tibor,

Thanks for your reply.
Unfortunately, we have no ability to perform any validation at the
client end.
Perhaps an explanation of the problem may elicit a better solution.
Basically, data from various sources is input into a screen.
The company that I work for wants to know which set of values has the
better precision so that they can decide which set of values to go
with.

Thanks,
Frank.
0
Reply Frank 12/17/2009 5:09:42 PM

Hi Plamen,

> DECLARE @x DECIMAL(38, 18);
> SET @x = 12.120340100;
> SELECT LEN(CAST(REVERSE(STUFF(CAST(@x % 1 AS VARCHAR(38)), 1, 2, '')) AS DECIMAL(38, 0)));

Thanks for the response.
The single line solution that you came up with seems to work well.
Can you explain what the line of code is doing please?

Many thanks,
Frank.
0
Reply Frank 12/17/2009 5:12:40 PM

In essence it trims the number to leave only the digits after the decimal point, removes the '0.' in front, reverses the 
  number and converts to numeric value to remove the trailing 0s, and finally takes the length. You can understand 
better by executing the nested functions one at a time starting with the innermost.

-- 
Plamen Ratchev
http://www.SQLStudio.com
0
Reply Plamen 12/17/2009 5:17:46 PM

Shouldn't the output from something that complex be 42??  :-))

-- 
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net


"Plamen Ratchev" <Plamen@SQLStudio.com> wrote in message 
news:GOmdne9P_PtdxrfWnZ2dnUVZ_tBi4p2d@speakeasy.net...
> Try this:
>
> DECLARE @x DECIMAL(38, 18);
>
> SET @x = 12.120340100;
>
> SELECT LEN(CAST(REVERSE(STUFF(CAST(@x % 1 AS VARCHAR(38)), 1, 2, '')) AS 
> DECIMAL(38, 0)));
>
> -----------
> 7
>
> -- 
> Plamen Ratchev
> http://www.SQLStudio.com 


0
Reply TheSQLGuru 12/17/2009 9:28:02 PM

Not exactly, but this will do:

SELECT REVERSE(STUFF(DATEDIFF(mi, 0, CONVERT(VARBINARY(3), 'Kevin G. Boles')), 2, 1, ''));

:)

-- 
Plamen Ratchev
http://www.SQLStudio.com
0
Reply Plamen 12/17/2009 10:38:21 PM

>> Is there an idiomatic way of determining the number of decimal places in a number (i.e. 0.123 = 3, 0.123456 = 6 etc.)?  <<

what does this mean?  If 0.123 is three then is 0.1230 three or four?
That is, are you asking about the *number* in the database or the
*display string* that happens to be used in the front end?

You can play with logs and this kind of thing to get the leading digit
count:

CAST (LOG10 (CAST (test_column AS INTEGER) AS INTEGER) = n
0
Reply CELKO 12/17/2009 11:04:37 PM

HAH!!! I am the answer to the Meaning of Life!!  I SO TOTALLY ROCK!!  :-D

-- 
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net


"Plamen Ratchev" <Plamen@SQLStudio.com> wrote in message 
news:GOmdnehP_PtGL7fWnZ2dnUVZ_tBi4p2d@speakeasy.net...
> Not exactly, but this will do:
>
> SELECT REVERSE(STUFF(DATEDIFF(mi, 0, CONVERT(VARBINARY(3), 'Kevin G. 
> Boles')), 2, 1, ''));
>
> :)
>
> -- 
> Plamen Ratchev
> http://www.SQLStudio.com 


0
Reply TheSQLGuru 12/18/2009 11:02:42 PM

15 Replies
2420 Views

(page loaded in 0.174 seconds)


Reply: