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 below in screen..