# 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)

#### 3 Comments.Leave new

• ScottPletcher
August 28, 2015 7:08 pm

I’d avoid any string-based methods because of the severe performance hit. Therefore, I suggest:

SELECT ABS(value) – FLOOR(ABS(value))
FROM #Numbers

• • 