Number of Decimal places and the SubString() function

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