I wanted a function that could give me the number of decimal places used in a given number. I really didn’t find any elaborate solutions on the web, so I decided to write my own by converting the number into a varchar and parsing it appropriately. While building the function, I discovered a neat little feature of the SubString() function.
Normally, SQL seems to work with an index of 1. So I if asked it the position of the “J” in “Jason”, it would return 1 (one). However, I was surprised to learn that the SubString() function accepts a 0 (zero) as a parameter for where to start the sub string.
This is an effective way to trim-off the last character of a string using SubString(@var, 0, len(@var)). For example, if I want to trim off the last character of “0040”, I can use the SubString() command to trim off the last “0”.
Here is an explanation with code:
--SubString(string, start, length)
Declare @var varchar(10);
Set @var = '0040';
Select SubString(@var, 1, len(@var)) --will return ‘.0040’
Select SubString(@var, 0, len(@var)) --will return ‘.004’
Using 0 for the start point with the length of the string has the effect of trimming the last character from the string. I wasn’t expecting this behavior, but it certainly is nice!
Decimal Count Function
Here is the entire function in case you are looking for something similar. I built it for use with SQL Server 2005.
Here is my usual disclaimer:
I’m not sure if this was the best way to go about solving the problem, but it worked. I thought my code and comments might be useful for someone else who has a similar problem. This code is free and comes with no guarantees.
One more thing - apparently I lost my formatting (tabs/spaces/etc) when I pasted this code, and I'm too lazy to fix it. Should only take a couple of minutes to make it look the way you're used to seeing it.
/*
DecimalCount
7/3/2008 JMJ
Count of the number of decimal places used after decimal point.
Params:
@numString varchar(MAX) varchar representing a number, assumes 1 (one) decimal point
Returns:
int Number of places used after decimal point, 0 (zero) if no decimal point
is found in the number. Will not consider zeros on the end of the number
(i.e. .0040 will consider only .004, which will return 3).
*/
CREATE function [dbo].[DecimalCount] (
@numString varchar(MAX)
) returns int
as
begin
declare @decimalPlace bigint;
declare @subStr varchar(max);
set @decimalPlace = 0;
--find index of decimal place
set @decimalPlace = Charindex('.',@numString,0);
if (@decimalPlace > 0)
begin
-- @subStr = string without decimal (or anything left of decimal)
set @subStr = @numString;
set @subStr = Right(@numString, Len(@numString) - @decimalPlace);
-- remove any trailing zeros
while(charindex('0',@subStr,len(@subStr))) = len(@subStr)
begin
-- catch conditions for numbers like '100.' or '100.0'
if ( charindex('0',@subStr,len(@subStr)) = 0
and len(@subStr) = 0 )
begin
break;
end
--using 0 for beginning will trim the last char from string
set @subStr = SubString(@subStr, 0, len(@subStr));
end
if (len(@subStr) is null)
begin
return 0;
end
else
begin
return len(@subStr);
end
end
else
begin
--no decimal place
return 0;
end
return 0;
end
