Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

Script to calculate Precision & Scale of a Decimal Value

By Lonely Rogue,

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)


SELECT                    RecordNo, RecordType

                                                                                                                ,Column2

                                                                                                                ,Column2_Violation=

                                                                                                                                                                                                                                                                CASE       WHEN LEN(CAST(REVERSE(PARSENAME(Column2,1)) AS INT) )>5

                                                                                                                                                                                                                                                                OR

                                                                                                                                                                                                                                                                                                                LEN( CAST(Column2 AS INT)) + LEN(CAST(REVERSE(PARSENAME(Column2,1)) AS INT) ) >8

                                                                                                                                                                                                                                                                THEN 'Y' ELSE 'N' END

                                                                                                                ,Column3

                                                                                                                ,Column3_Violation=

                                                                                                                                                                                                                                                                CASE       WHEN LEN(CAST(REVERSE(PARSENAME(Column3,1)) AS INT) )>4

                                                                                                                                                                                                                                                                OR

                                                                                                                                                                                                                                                                                                                LEN( CAST(Column2 AS INT)) + LEN(CAST(REVERSE(PARSENAME(Column3,1)) AS INT) ) >6

                                                                                                                                                                                                                                                                THEN 'Y' ELSE 'N' END

                                                FROM

                                                (

                                                                SELECT Column1=77.037030,Column2=0.0409700, Column3=0.30570,Column4=0.169610,Column5=NULL,RecordNo=1,RecordType='Good'

                                                                UNION

                                                                SELECT Column1=77.037037,Column2=0.040979, Column3=0.30571,Column4=0.169619,Column5=NULL,RecordNo=2,RecordType='Bad'

                                                )Table1

 

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.

Total article views: 881 | Views in the last 30 days: 3
 
Related Articles
FORUM

convert a number to decimal

convert a number to decimal

FORUM

PARSENAME

Comments posted to this topic are about the item [B]PARSENAME[/B] Thanks for Good question.. "Eid...

FORUM

Numbers - getting the right decimal places

how to get the correct scale

FORUM

Remove Decimals Without Rounding

How do I remove decimals

FORUM

Reverse string without built in functions

reverse string

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones