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

15 Replies
4170 Views

Similar Articles

[PageSpeed] 57

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

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

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

Similar Artilces:

Email Subject Auto Number ?
Hi I need Your Help 1. When i send an email through the activities it sent with an automatic number in the subject. what this number means ? How can I take it off ? after I'll take it off what does it means ? the return email would not be related to the entity ??? 2. I have an attachment in the note in the opportunity entity How can I send an email with this note as the attachment of the mail ??? -- Thx very much , Omry Hi Omry, The number you see in the subject line of the email is the tracking token. This will be used to identify the email as a CRM activity and relate it to an ac...

Display one number in a cell even though 2 numbers get pasted into the same cell? #2
Ok. Maybe I didn't explain myself thoroughly. I don't care about the second number. I just want to get rid of th second number in the cell and only keep the first. Can I do this by using Data - Seperate Text into columns? If so, ho do I do this? Any other possible suggestions? DrSues0 -- DrSues0 ----------------------------------------------------------------------- DrSues02's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1256 View this thread: http://www.excelforum.com/showthread.php?threadid=26621 Yep. Select that column of cells. data|text t...

Number of emails in a folder
For some reason Entourage does not tell me how many emails are in a folder on the IMAP server and I had to log into the web based client to see this. Can Entourage tell me this? John -- Are there errors in the Bible? Do Animals go to Heaven? Is drinking Alcohol a sin? Is the KJV the only translation to be used? If you want to learn, get answers, and be able to defend the faith, CERM is your place. http://www.cerm.info/ John Wolf wrote: > For some reason Entourage does not tell me how many emails are in a folder > on the IMAP server and I had to log into the web bas...

My places Bar
Hello, I am using office 2003, Recently I noticed that the "my places" bar has disappeared. I have looked everywhere on how to put it back and have had no luck. I have done registry entries with no luck. I did not install any recent software. I have also tryed to repair office aswell. Any thoughts? Thanks, I do not think the "My Places" bar is a default toolbar, but rather a custom toolbar that you (or a predecessor) installed. Perhaps the file for it got deleted? -- Best Regards, Luke M *Remember to click "yes" if this post helped y...

Filter Records not Begginning with Numberic Characters
My table has a Street address field. I would like to filter out records that do not begin with a numeric Character. Also if possible I would like to filter records that do not have a space between the numeric characters and the alpha - Example: 123Street I would try WHERE StreetAddress Not Like "[0-9]*" or StreetAddress Not Like "*[0-9] *" John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County Jeff wrote: > My table has a Street address field. > I would like to filter out records that do n...

How can I add numbers in my text files made by my MFC app ?
I want my MFC app to add the numbers in my saved text on saving time like this... 1:here 2:test 3:hello 4:hehe 5:pizza 6:hi Thanks, BMXer This is just example code I found on google. #include "stdafx.h" #include "string.h" int countLines(char filename[]); int main(int argc, char* argv[]) { int lineCount; char filename[80]; strcpy(filename, "test.txt"); lineCount = countLines(filename); printf("Lines in %s: %d\n", filename, lineCount); return 0; } countLines(char filename[]) { FILE *fileRef; int lineCount = 0; char fileLine[256]; ...

Random numbers #4
I would like to assign random numbers to a list of names. I tried using Randbetween, but it allows numbers to be repeated. Is there a way to assign numbers without any repetition? See: http://www.mcgimpsey.com/excel/udfs/randint.html HTH Jason Atlanta, GA >-----Original Message----- >I would like to assign random numbers to a list of names. I tried using >Randbetween, but it allows numbers to be repeated. Is there a way to assign >numbers without any repetition? >. > ...

determine mail flow problem!!!!!!!!
Help! :-( Our mailflow from 2 backend 2003 exchange servers keeps queuing up in the outbound queue (which is set to always run delivery). Messages stay in there from 10 to 120 minutes before being sent to our gateway MTA and then sent off site (to hotmail.com for example). However when I telnet from a backend server to the gateway over port 25 and send a test message it goes right away. I can't for the life of me figure out why. One example when viewed in message tracking history says: 10:16am - smtp: store driver, message submitted from store 10:16am - smtp: message submitted to ...

ID number for all emails
We have a need to have all emails have a unique id number so they can be tracked - does anyone know if this is possible? All items in Outlook are uniquely identified by a combination of the EntryID of the item. This is a constant unless the item has been moved (it remains the same if moved in a PST file) in an Exchange mailbox or public folders store. In addition, each item has a ConversationIndex property that in combination with the ConversationTopic property. Those could be used. Other than that you could generate your own unique series of tags for items by using a database table's ...

Customise field to show as % or round up the numbers
Hi, I am trying to add a new field / attribute to CRM quote entity. This field will be showing the profit margin on the quote form. Is there anyway for me to format the field, so it will appear as %? And if it is all possible for the calculation to round up to the next number? Ta! Sindy There's no default format option to do that, so you would have to use the OnLoad and OnChange javascript events to format and round up your field. HTH, -- Jeffry van de Vuurst CWR Mobility www.cwrmobility.com -- SW wrote: > Hi, > I am trying to add a new field / attribute to CRM quote entit...

Determine columns used
I have 100+ spreadsheets which i have to edit into a certain format. The spreadsheets currently have 100 columns (from 1-100). From there i have to reduce the number of columns set to 8 columns. E.g: 1 2 3 4 5 6 7 8 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 (from column 9 onwards, it goes to the 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 next rows' column 1) 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 How do i do that without altering the order of the value of the cell? Let me make sure we understand the question. You have ...

Default number format
Is there a way to change the default NUMBER format for new workbooks? I found the default FONT, but not default NUMBER format. Dave French David Open a new workbook. Customize as you wish. File>Save As Type: scroll down to Excel Template(*.XLT) and select. Name your workbook "BOOK"(no quotes). Excel will add the .XLT to save as BOOK.XLT. Store this workbook in the XLSTART folder usually located at........ C:\Documents and Settings\username\Application Data\Microsoft\Excel\XLSTART This will be the default workbook for File>New>Workbook. You can also open a new wo...

Dynamically determining when a month ends
Hello all, Ok, I have a simple excel spreadsheet, where I keep track of my spending on one tab, my income in another and my summary in a thrid. My problem happens when I try to dynamically calc. avg's for months in the summary page. For instance, if I want to see how much money I spent on average per month, or even per day, I draw from the data entered into the spending page. Now, the spending page is set up with the following columns: Date:: Description:: Amount The problem with this is that I can't predefine a max row # for each month, since I might have 80 entries in spending...

Modification of Part Number (Revised)
I would like to add the string "1212" (no quotes) to some part numbers. Essentially, I have two versions of part numbers to modify: xxxxxxx(space)(space)xxxxxxxxxx(space)xxxxx(space)xxxx or xxxxxxxxxx(space)xxxxx(space)(space)xxxxxxx(space)xxxx(space)xxxx The length of the xxxxxx strings varies and represents alphanumeric characters that must remain unchanged. The string "1212" (no quotes) must always be appended to the first xxxxx string of alphanumeric characters. Thus the end result of the modification should be as follows: xxxxxxx1212(space)(space)xxxxxx...

Verifying number of records for upgrades/migrations
I have the following script from an MBS upgrade manual, which works great: Select name, rows from sysindexes where indid in (0,1) order by name However, we need to change our sort order to case-insensitive, according to the TK on CUstomersource using BCP. I have done this a couple times, but after you bcp the data out, then in, the number of records signicantly changes because the script is looking at the sysindexes table. Is there a script that we can run to look at the number of records within all user-defined tables, instead of the number of indexes? I don't feel comfortable...

Page numbers don't appear in subreport
Hi all, I have a simple problem with a report. In the page header of the report I list page x of z in the usual fashion. However, the report contains a subreport and if that subreport runs into multiple pages the main report page header doesn't print the page number on those pages. Any ideas to force the main report page header to print on every page even if the only data on the page is from the subreport? Dannasoft wrote: > Hi all, > > I have a simple problem with a report. In the page header of the > report I list page x of z in the usual fashion. Ho...

how to determine the size of the sheet
Hi I had a collegue who told me once a way to dtermine the number of the rows in a sheet of the workbook I am working at. It is known that when you open a new workbook each sheet has maybe 65365 or something like that rows. MY QUESTION IS: HOW TO MAKE THE SHEET 1000 ROWS OR WHATEVER NUMBERS OF ROWS I ONLY NEED? Thanks in Advance, Ahmed Hi Ahmed The number of rows and columns are fixed. Your workaround would be to hide the ones you consider unneeded. HTH. Best wishes Harald "Ahmed SHEBL" <ahmad.shebl@hotmail.com> skrev i melding news:%231uFO3IcHHA.4720@TK2MSFTNGP0...

length of check number
I recently got a new set of paper checks that started at #50000. When I download the cleared check information for these, MS Money is truncating the check number - so 50001 became just 1. Is this a known issue? I contacted Wells Fargo, but they didn't know anything about it. I can edit the check number and change it to 50001, so it seems to be able to hold that number of digits. Any pointers/suggestions would be greatly apprecaiated. Henry I'm betting if you checked the downloaded data, Wells Fargo is truncating it not Money. "Henry Winkler" <Henry Winkler@di...

How to extract the Number from a String
Hello friends I have a column with following type of data: 1243 no. of Books 213 no. of Pens What i want is to extract only the numeric data from the column and keeping the the original string as it is thanks in advance johnbest -- johnbest ------------------------------------------------------------------------ johnbest's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=29694 View this thread: http://www.excelforum.com/showthread.php?threadid=494535 Hi John Assuming your data always has a space after the last numeric value, and with the data in A1 =--LEF...

Turn off Automatic Check numbers?
Does anyone know how to turn off the Automatic Check numbering within a checking account? This is particularly annoying when we put in a new withdrawal for a ATM Cash withdrawal or direct debit and a check number comes up. ...

when I type a long number it shows up as smaller number and +
when converting from xls to csv format, some of my longer mortgage numbers get condensed into a smaller number with a letter, a plus sign and another number. example: 100020013120 turns into 1.0002E+11. Any way to get rid of this? Thanks, Yes, just specify the format that you want. Format Cells...>Number>0 decimal places Regards, Fred "SandyC" <SandyC@discussions.microsoft.com> wrote in message news:FA52E426-360E-4BE5-B63B-1209E673CA65@microsoft.com... > when converting from xls to csv format, some of my longer mortgage numbers > get conde...

Change outline numbering, hanging indent not updated
I followed http://www.shaunakelly.com/word/numbering/OutlineNumbering.html to define an outline numbering scheme. For each additional outline level, I added 0.12" of extra indentation for the text of the heading. The tabstop settings are properly updated in each Heading style, but the hanging indents is not. I manually modified the hanging indents to match the tab settings. Is there a more intelligent way to have the hanging indents update automagically? When you apply outline numbering to paragraph styles, the indents and tabs must be set in the Numbering dialog, not in the...

Displaying a 12-digit Number
How can I have a cell display a 12-digit number? Currently, when you would type the number 123456789012, 1.23457E+11 is displayed. As I need to have the spreadsheet saved as a Text Delimited file, even when formatting the cell as text does not work, because when the file is being converted, it sees the cell as a number, and brings me back to square one. Any ideas?? Thank you. Hi format the cell with the custom format 000000000000 -- Regards Frank Kabel Frankfurt, Germany John wrote: > How can I have a cell display a 12-digit number? > Currently, when you would type the numbe...

How do I determine
I have X number of rows that show employee names and a column (A2) that lists the value of products sold by each employee and I want to pay them a percentage based upon value of sales. For example: If they sell up to =A31,000 worth of products, I will pay them 3% of the value. Between =A31,000 & =A32,000, I will pay them 5% of the value Over =A32,000, I will pay them 10% of the value The amount paid to them in commission will be show in column (A3). Many thanks D.Haste darren.haste@ttt.co.uk "Hastey" <darrenhaste@sky.com> wrote in message news:6a0a4fa9-adcd-484a-80...

How do I update a spreadsheet with numbers input into another?
How do I link two speadsheets in order to update both at the same time? "Duma" wrote: > How do I link two speadsheets in order to update both at the same time? Probably something like this In Sheet2, In A1: =IF(Sheet1!A1="","",Sheet1!A1) Copy A1 across/down to cover the extent Sheet2 will then reflect entries/updates in Sheet1 for the formulated range -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- If both spreadsheets are the same, headers, data ranges, titles etc, basically same architecture on both sheets, then... While on Sheet1...