In my earlier post on “SQL SERVER – Different Methods to Extract Scale Part From Decimal Number“, I had shown you how to extract only the decimal part from a number. The post had three different methods to Extract Scale Part From Decimal Number. Recently I have found a very simple method to extract the decimal part and want to share with my blog readers.
Let us use the same data set used in the previous blog post
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);
Now use the very simple modulus function. Modulus function returns the remainder of one number divided by another number. The logic is to find the remainder after applying the modulus function and use 1 as a divisor.
SELECT VALUE , ABS(VALUE)%1 AS DECIMAL_ONLY FROM #NUMBERS
The result is shown below. Note that it returns only decimal part from the numbers.
I found that this is a very simple method of all other methods listed in my earlier blog post. Let me know if you are aware of any other methods which are more efficient and simple to use to find Scale Part From Decimal Number.
I will be happy to post that on the blog with due credit to you. Leave a comment with your script or just send me at pinal at sqlauthority.com
Reference: Pinal Dave (https://blog.SQLAuthority.com)