SQL SERVER – Simple Method to Extract Scale Part From Decimal Number

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.

SQL SERVER - Simple Method to Extract Scale Part From Decimal Number decimal

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.

SQL SERVER - Simple Method to Extract Scale Part From Decimal Number decimal_result

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)

Data Type, SQL Scripts, SQL Server
Previous Post
SQL SERVER – SSMS – Enable Line Numbers in SQL Server Management Studio
Next Post
SQL SERVER – Microsoft Azure – Unable to Find Higher Tier Series Virtual Machine to Upgrade

Related Posts

3 Comments. Leave new

  • Hi Pinal,

    Nice article. Applying what we learn in our childhood real time :)

    Thanks,
    Srini

    Reply
  • 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

    Reply
  • ABS(VALUE)%1 doesn’t work, returns 0 every time.

    Reply

Leave a Reply