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)
Nice article. Applying what we learn in our childhood real time :)
How do I store and retrieve exact precision when that precision is different. For example I would want to retrieve 12.87 and 390.1 (not 390.10 if I used decimal(5,2)) from your above example. I had been using float data type, but I recently learned that float is returning 0.04 for 0.0400 and I need it to be 0.400 (they need to be really precise). I have been searching for quite a while now and not coming up with a good answer.
Thanks in advance if there is a good answer for this
ABS(VALUE)%1 doesn’t work, returns 0 every time.