Did you ever happen to fall in a situation of calculating the Precision & Scale of a DECIMAL/NUMERIC column? Well, I had recently.

For others- Precision means the total number of digits in a Numeric value, both to the right and left of the decimal point. Scale refers to the total number of digits after the decimal point. So, for 77.03703 the Precision is 7 and Scale is 5.

While my table was configured to hold the data to a greater extent, another system which accepts our data restricted the Precision & Scale attributes. So, I had to scribble a script that could actually tell me if the Precision & Scale of a column are within the stipulated limits.

So, what I’ve to do is –

1. To break the Decimal data into two parts –one number before the decimal point and the other after the point.
2. Then find the length (no. of digits) of these two numbers.
3. Attribute pass or failure status to that value of that record.

 And to add a note, the trailing Zeroes after the decimal point must not be counted as a digit hence has to be disregarded during the calculation of length. For e.g. In the value 77.0370300 , the last 2 zeroes must not be accounted in calculating the length of the Scale. So, the effective length comes to 5 considering just the number “03703”.

Armed with these facts, let’s proceed to build the script.

1. Breaking up of the decimal data could be done in many ways including the (in)famous usage of SUBSTRING. But SQL Server has a tidy function to do this – PARSENAME(). And in my experiments, I’ve witnessed that SUBSTRING has more operational cost than PARSENAME.

PARSENAME() function in a way splits the string based on the ‘.’ as delimiter and expects two parameters – the string & part of the string. This is meant for dissecting the Four Part Naming Conventions into the server name, the database name, the owner name, and the object name. However, this can be employed here in bisecting our decimal values.

So, a sample output & the execution of this –

SELECT ObjectName=PARSENAME('Server1.AdventureWorks.Sales.Contact', 1), SchemaName=PARSENAME('Server1.AdventureWorks.Sales.Contact', 2), DatabaseName=PARSENAME('Server1.AdventureWorks.Sales.Contact', 3) , ServerName=PARSENAME('Server1.AdventureWorks.Sales.Contact', 4);

Now employing this with a decimal value –

SELECT PARSENAME(77.0370300,1)

So, we could extract the Scale part and feeding this value to the LEN() function would give us the number of digits. But as noted above, the trailing Zeroes must not be accounted. To tackle this we could divide it by 10 to remove trailing zeroes, but there’s no way this could be done in one go only because of the fact that we wouldn’t be aware about the number of 0s in the end.

            Then I’ve used the REVERSE() function – which would literally reverse any string , and a CAST() function to type cast the value to INT data type wherein the Zeroes that were moved into the front because of the earlier ‘REVERSE’ing gets eliminated. A glimpse of this implementation is below –

SELECT PARSENAME( 84.274190,1),REVERSE(PARSENAME( 84.274190,1)),CAST(REVERSE(PARSENAME( 84.274190,1)) AS INT),REVERSE(CAST(REVERSE(PARSENAME( 84.274190,1)) AS INT))

2. Finding the number of digits can be done using LEN() function, which tells us the number of characters in a String.

SELECT LEN(CAST(REVERSE(PARSENAME( 84.274190,1)) AS INT))

3. Tagging the evaluation status would then be done using comparison operators between the output of LEN() and our criteria.

Now, putting all these pieces together, a script would emerge like this which is meant to evaluate if the two columns of a table are in line with the below specifications.

 Column2 Numeric (8,5)

Column3 Numeric (6,4)

This script can also be made into a UDF, for enhanced reusability and to gain the ability of using it for table with several columns.

-- In 'thoughts'...

Lonely Rogue.