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

```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
Frank
12/17/2009 12:46:06 PM
sqlserver.programming 1873 articles. 0 followers.

15 Replies
3395 Views

Similar Articles

[PageSpeed] 31

```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
> 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
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
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
> 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
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
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

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
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
> 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
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
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
Plamen
12/17/2009 4:34:12 PM
```Hi Tibor,

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

"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
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
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
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

"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
TheSQLGuru
12/18/2009 11:02:42 PM

Similar Artilces:

Determine field from attribute
With Attribute.GetCustomAttribute() I can obtain the attribute if I have the field. How can I achieve the opposite, how can I obtain the field from within the attribute? I want to check the field type in MyAttribute constructor, and throw exception if attribute and filed mismatch. Something like more detailed AttributeUsage functionality. Hi Mihajlo Cvetanovic, You wrote on 19/02/2010 : > With Attribute.GetCustomAttribute() I can obtain the attribute if I have the > field. How can I achieve the opposite, how can I obtain the field from within > the attribute? I want ...

Determine if data label overlaps chart title?
Hello again, all you experts. I now have run into a new issue when generating charts via VBA. I add data labels only if the datapoint belongs to a particular company. Sometimes, the data label is on the last (and highest) point on a distribution curve. It then runs into the title of the chart. I've been trying to capture this in code but am at a loss. I can't find any property of the data label that tells me its top. I haven't even gotten to trying to compare this with the bottom of the chart title! What I want to do is change the data label's position to bottom i...

Help with summing numbers in cells that also contain text
Hello all, I have a work schedule where the column heading is the employee's name and the cells below indicate what the employee's status was for that date, as follows: col A col B Date Smith 01/01/07 8.0 SIC 01/02/07 4.0 SIC 01/03/07 D (indicating worked the day) 01/04/07 8.0 VAC TOTAL SIC Hrs: 12.0 I've tried extracting the numerical values into a hidden helper column "C" using the following formula: =IF(RIGHT(B2,3)="SIC",LEFT(B2,3),"") It extracts the correct value, how...

How do we determine crew size with Microsoft Project?
How do we determine crew size with Microsoft Project? Crews for what, to do what, with what? How many hours of work have to be done, what constraints are there (physical, safety productive etc)? Which version of project? Project is a calculator, like Excel. unless you enter data it can't calculate anything! Tell us a lot more and maybe we can help. -- Rod Gill Microsoft MVP for Project - http://www.project-systems.co.nz Author of the only book on Project VBA, see: http://www.projectvbabook.com "HarperMatrix" <HarperMatrix@discussions.microsoft.co...

number format #15
normal format for number & currency in excel is xxx,xxx,xxx,xxx.00 How to configure numbers as well as currency as xx,xx,xx,xx,xx,xx,xx,xx,xxx.00 format ? when custom format is used, the entry is converted to thousand comma separator format. pl help Here is an answer from Bob Phillips to a similar request ====================================================== Hi Rajiv, I think you may be intrested in the add-in I have modified for the Indian numbering/currency system. The original code is available on MS site for English system. The add-in is attached in zipped format. If you nee...

Equating a Percentage to a Number
I am a new excel user. I am trying to set up an electronic grade book and I wanted to know is there is a formula I can use to tie a letter grade to a percentage. Thank You You are a bit vague, do you have number grades or letter grades? =IF(A1="","",VLOOKUP(A1,{0,"F";0.6,"D";0.7,"C";0.8,"B";0.9,"A"},2)) that means 0-59% F, 60-69 D and so on, replace them with numbers 1-5 if needed or change the whole scale if needed -- Regards, Peo Sjoblom "Laura" <anonymous@discussions.microsoft.com> wrote in mes...

Entering a 16 digit number
I am creating an Excel worksheet and am trying to enter credit card numbers in one of the columns. When I enter the number, it automatically changes the last digit to a "0". How do I format the cells to accept a 16 digit number? Format as text or precede the entry with an apostrophe, excel has 15 digits precision but I assume you won't do any calculations with credit card number -- Regards, Peo Sjoblom (No private emails please) "Tbird" <Tbird@discussions.microsoft.com> wrote in message news:EF7E2877-8F24-4F80-883D-30C9679151FE@microsoft.com... >I ...

Determining the Active Worksheet
My problem here is of course in line 1. I need the code to run based on what worksheet is active. There will be a series of these if statements (if May is active I will select May - September and so on). So is there any way to write the first line to determine the active worksheet? Sub Macro2() If Worksheets("April").Active = True Then Sheets(Array("April", "May", "June", "July", "August", "September")).Select End If End Sub Hi ordnance1, For your first line you can use: If ActiveShee...

how to insert roman numbers
I am trying to insert roman numerals such as Grade 2 For Roman 2, have you tried holding the shift key while pressing the i key twice? -TedMi "drisc229" <drisc229@discussions.microsoft.com> wrote in message news:A1DE5488-E49E-4F81-8539-2E87A9662BD9@microsoft.com... >I am trying to insert roman numerals such as Grade 2 How do you want to use the Roman numerals? If you want a numbered list, just choose the desired numbering format (i,ii,iii or I,II,III). If you want a sequential list with entries anywhere in the text, use SEQ fields. If you want static ...

How do I determine if I have Exchange?
"Home users typically do not have an Exchange Server e-mail account; instead they use a POP3 e-mail account with an Internet service provider (ISP) or use a web-based e-mail service, such as MSN Hotmail. If you use a POP3 e-mail account, your ISP can provide you with your specific account information. People without Exchange Server accounts cannot use the features in Outlook that require an Exchange Server." The above message appears in the HELP file, but I still need help determining if I do have an "Exchange Server"? Thank you. Did you purchase Exchange Server and ...

determining identical ranges
Can someone refresh my memory as to the array formula one would use t determine if the data in two ranges are identical? Also, how would I return an array of the row numbers of the rows i Range A that are not identical in Range B? Thanks Steve Przyborski Boston, Mas -- Message posted from http://www.ExcelForum.com Steve, =IF(AND(A2:A6=B2:B6),"Identical", "Mismatch") Array-entered. Not case sensitive. -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "upstate_steve >" <<upstate_steve.156g8e@...

Auto
I have been working with a customer who was required to migrate from Lawson software. They were used to Lawson automatically generating batch numbers for them in Payables and Receivables and I think General Ledger as well. They think it is odd that GP doesn't create batches automatically. My basic question is anyone aware of a valid tested third party solution that would autocreate batches for at minimum Payables and Receivables? Thanks much - Adam, Its very easy to be customized, we already customized such solution for one of our customers to automatically generate batchs base...

Getting error number 0x8004011D
This is a multi-part message in MIME format. ------=_NextPart_000_0019_01C632ED.8AE2B8B0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I got err msg ' Task " Microsoft Exchange Server reported error = (0x8004011D). The server is not available.... " whenever I send out mail = from this particular PC. Receiving of mails are working fine but not sending out mails. Other PC clients can send/receive mails from this Exchange svr ( = SBS2003).=20 Wonder what have I missed out? Thanks Jim ------=_NextPart_000_0019_01C632ED.8A...

Comparing one list of numbers against another
Hi , Can someone help me with this problem I want to compare a list of tape media with another , The results should show missing media and new media in separate columns An example First Inventory Second Inventory Missing Media New Media TAPE01 TAPE01 TAPE07 TAPE11 TAPE02 TAPE02 TAPE08 TAPE03 TAPE03 TAPE09 TAPE04 TAPE04 TAPE10 TAPE05 TAPE05 TAPE06 TAPE06 TAPE07 TAPE11 TAPE08 TAPE09 TAPE10 Any help appreciated Regards Mike Mike,...

Receivings Number
Hi, When I am doing my purchase receiving at the Receivings Entry, I sometimes encounter some problem which the Receivings Number. For example, suppose the new receivings number should be REC/03/000004, but this number REC/03/000005 is given to me instead. What could be the possible cause of the missing receivings number:REC/03/000004. (this no. is not used by anyone). Please advise or tell me how can I avoid. Thanks in advance. I believe it's when someone started to do a receivings but then deleted it. It already increments to the next number. You can prevent deleting of documen...

Calculate total number off cells with data in
Hi All Hope I find you well I have a spreadsheet that has a number of cells that may or may not have data in them depending on curtain criteria. What I would like to be able to do is count the number of cells with data in (data is alphanumerical) and display the total number of cells with data in in a different cell. Any ideas? Hi Gazza, > What I would like to be able to do is count the number of cells with data in (data is alphanumerical) and display the total number of cells with data in in a different cell. Any ideas? > Check out the COUNTA worksheet function. Regards, Jan Kare...

This happens in 2007, 2003, and 2002. My doc has 2 "next page" section breaks within the first 3 pages. After each break, I've requiested that the numbering restarts at "1" and that works fine. After several pages I need 2 "Continuous" page breaks so I can use a 2-column format within a page. I use the Insert Page Number Format command to have it CONTINUE numbering. However, it restarts the numbering. If I do this as a test with a doc that does not have regular section breaks at the beginning it seems to work. Best case, I would like to ma...

Splitting Numbers in a Column
:confused: I have a column of numbers that are 21 digits long. For each number the first 7 digits represent a department, the next 5 digits represent a class and the last 9 digits represent the account #. Is there a simple way to separate the single column of numbers into 3 columns with the appropriate headings? An example of the type of numbers in the column is as follows: 100310310000513000000 105210010000521000000 101900020000521000000 105400010000521000000 105900020000723000000 104600010000522000000 101710010000524000000 102600020000521000000 10262212000052100000 -- jer10 ----------...

Checking Winning Numbers in the Lottery.
For people who may not understand the lottery rules for the UK. Person picks 6 numbers in the range 1 to 49 (no duplicates) If all the 6 numbers come up then when the lottery numbers are announced, then it is serious party time. Problem: - We have 25 people in our Lottery syndicate and therefore I want to automate the checking of numbers that are announced with numbers that are pre-selected by the 25 individuals. Additionally, I want to know how many numbers have matched in an individual row against those announced. E.G. Assume lottery number announced (in ascending sequence are) 1, ...

Any way to convert column of numbers from positive to negative?
Hi all, Is there any way to convert a column of numbers that are positive to be negative? We create a text file from our general ledger program that has all the transactions for the past year, then import that file into Excel. We use the text to columns to convert the text file to a usable format where the debits are in one column and the credits in another. The problem is that all the numbers are shown as positive so when we net various figures the amount is not correct. Here's an example: DR-amount CR-amount 8,239.32 23,545.26 18,028.12 200,000.00 ...

How to create CMainFrame-based dialog to display variable number of listboxes in a CFormView
Hello, I have an app where I need to display a variable number of CListBoxes (in a single row) in a CFormView. I don't know how to dynamically create the CListBoxes and attach them to the CFormView, something that would normally be done through resources if it were a fixed number of listboxes.. Could anyone give me an idea of how to dynamically allocate and attach controls and then any cleanup? I also need to know how to manually hook up a CMainFrame with a CFormView, as the AppWizard only creates something like that as a topmost window, I think. Any help would be greatly appreciate...

Numbering Lines and Paragraphs
I'm using MS 2007. I'd like to number the lines of a doc, but restart the numbering with each new paragraph. The only options I see are: Continuous Restart Each Page Restart Each Section Suppress for Current Paragraph Is there any way I can change the settings, short of making every paragraph it's own section? (I'm doing some editing for a project and we use shorthand to reference where to make changes, such as: P10 L15 [paragraph 10 line 15]) Thanks! To clarify... I also need the line numbering to start new at each paragraph so in the example (...

Convert text numbers to number format
I have received a file that has a date column. However, the column is reading as text. I need to change the column so that the date reads as a number format. Any ideas? -- montagu ------------------------------------------------------------------------ montagu's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=15227 View this thread: http://www.excelforum.com/showthread.php?threadid=314927 Right click and reformat as you want then click on cell and press F2 an enter to refresh data that should do the tric -- scottymellot --------------------------------------...

How do you determine which certificate template to use
We are trying to setup RPC/HTTP and have had numerous problems. We think it has something to do with the certificate and SSL. How do we determine which certificate template to use? Thanks On Tue, 1 Feb 2005 12:15:06 -0800, "RP" <RP@discussions.microsoft.com> wrote: >We are trying to setup RPC/HTTP and have had numerous problems. We think it >has something to do with the certificate and SSL. How do we determine which >certificate template to use? > >Thanks You shouldn't need to use a certificate template, it's just a computer certificate. Run MMC, a...

duplicate voucher number the same payment number exist
Two different transaction but same payment number. One is already posted that's why the error appeared duplicate keys every time i posted the other transaction. Why is it that there is a duplicate number since the transaction made in different time although same day! I saw it in pm10300. i need to post the other transaction but i cannot post because of that posted payment number exist. What can i do? any body can help me regarding this matter. thank you! magar I would delete the payment and redo the task. -- Regards, -- Mohammad R. Daoud MVP, MCP, MCBMSP, MCTS, MC...