SQL SERVER – Different Methods to Extract Scale Part From Decimal Number

One of my blog readers asked me “Is there a way to extract only the number after the decimal point?”. For example, in decimal number 45.90, 45 is the precision and 90 is the scale value.

There are several methods to get this done

Let us create this dataset

CREATE TABLE #Numbers (value DECIMAL(16, 3));
INSERT INTO #Numbers (value)
VALUES (100.00);
INSERT INTO #Numbers (value)
VALUES (- 23.89);
INSERT INTO #Numbers (value)
VALUES (390.077);
INSERT INTO #Numbers (value)
VALUES (12.87);
INSERT INTO #Numbers (value)
VALUES (390.1);


Method 1 : truncate scale part and subtract it from the original number

SELECT VALUE , ABS(VALUE-CAST(VALUE AS INT)) AS DECIMAL_ONLY FROM #NUMBERS

Method 2 : Convert the number into a string and get the number after the decimal point

SELECT VALUE , SUBSTRING(STR(VALUE,16,3),CHARINDEX('.',STR(VALUE,16,3))+1,LEN(VALUE)) AS DECIMAL_ONLY FROM #NUMBERS

Note that str function is a string representation of a number. The second parameter 3 restrict the number to have maximum of 3 digits and identifying the position of a dot, we will be able to extract only scale part

Method 3 : Use pARSENAME function

SELECT VALUE , PARSENAME(VALUE,1) AS DECIMAL_ONLY FROM #NUMBERS

Parsename split the data based on the dot and parameter value 1 is used to extract the right most part which is scale in the decimal number

The result of the able queries are

Reference: Pinal Dave (https://blog.sqlauthority.com)