SQL - How do I get only the numbers after the decimal?

Question

How do I get only the numbers after the decimal?

Example: 2.938 = 938

Datatype? For bigint use `SELECT substr((2.938 % 1)::text,3)::bigint;`, else use text result. — Peter Krauss, Jan 03 '22 at 12:08

score 147 · Answer 1 · answered Aug 05 '10 at 19:59

147

try this:

SELECT (num % 1)

answered Aug 05 '10 at 19:59

Pavel Morshenyuk

10,891
4
32
38

12

Only seems to work if num if a DECIMAL. Fails for me when num is a FLOAT. – dsz Mar 19 '14 at 04:19
In cases like SELECT (100.0001 % 1) the result shown contains '0.' part, that is 0.0001. But as per the question required part is '0001' – Tejasvi Hegde Jul 16 '14 at 10:00
1

Microsoft SQL Server 2000 throws `Msg 8117, Level 16, State 1, Line 1 Operand data type numeric is invalid for modulo operator. Msg 206, Level 16, State 2, Line 1 Operand type clash: int is incompatible with void type`. Works nicely 2005 onwards. – jumxozizi Aug 24 '16 at 10:59
Very nice! used also in C#, obviously without the SELECT, in fact this was too obvious! Hehe, but I guess sometimes is faster to find the answer than thinking a little bit, where we gonna stop!? Ha! – Gabriel G Dec 29 '16 at 18:35

score 69 · Accepted Answer · answered Aug 05 '10 at 19:53

69

one way, works also for negative values

declare @1 decimal(4,3)
select @1 = 2.938

select PARSENAME(@1,1)

answered Aug 05 '10 at 19:53

SQLMenace

132,095
25
206
225

2

So what if the value is .042 it only returns 42 then? – user380432 Aug 05 '10 at 20:07
no, it returns 042, both of these return 042--> SELECT PARSENAME(0.042,1), PARSENAME(.042,1) – SQLMenace Aug 05 '10 at 20:10
If you want only 42, then select cast(PARSENAME(@1,1) as int) – Joe Stefanelli Aug 05 '10 at 20:10
Hmmm for some reason I'm getting 42 --Get remainder of day that was not worked DECLARE @NotWorked DECIMAL(4,3) SET @NotWorked = (@AllMins/@MinsInDay) DECLARE @Remainder INT SET @Remainder = PARSENAME(@NotWorked,1) 42 and 3.042 All Mins is INT MinsInDay is Decimal so 4380/1440 – user380432 Aug 05 '10 at 20:13
2

I don't know what your numbers are but try this DECLARE @NotWorked DECIMAL(5,3) SET @NotWorked = (144/4380.0) select PARSENAME(@NotWorked,1) it gives me 033 – SQLMenace Aug 05 '10 at 20:20
IS there a way to do this without the select I am actually try to give a @Remainder variable the value.... which in your can above would be the 033 or the number after the decimal – user380432 Aug 05 '10 at 20:24
3

can't be an int since an int will dismiss the first 0, use varchar, example DECLARE @Remainder varchar(10) DECLARE @NotWorked DECIMAL(5,3) SET @NotWorked = (144/4380.0) SELECT @Remainder = PARSENAME(@NotWorked,1) select @Remainder -- see 033 – SQLMenace Aug 05 '10 at 20:27
3

This doesn't work if you pass it a float variable with zeros after the decimal. Example, `DECLARE @AMT FLOAT SET @AMT = 3881.00 SELECT PARSENAME(@AMT, 1)` It returns `3881`. Ok, so I see that you are specifically declaring the variable as decimal, but I think it should still be menioned. – LittleTreeX Sep 20 '13 at 15:04
2

It will not work if there is no fraction part. Example: select PARSENAME(1,1) returns 1. Which is wrong, it should ideally return either 0 or blank or null – Tejasvi Hegde Jul 16 '14 at 09:51
The question is about numbers after the decimal..if you have a decimal with scale and precision you will never get this issue since it will then become 1.00 or something like that – SQLMenace Jul 16 '14 at 15:23
This answer returns "Arithmetic overflow error converting numeric to data type numeric." IF the whole number is 2 digit and above. e.g. 10.1234. – Gellie Ann Feb 25 '15 at 01:45
ROUND can truncate as well since 2008. ROUND(Value, 0, 1) – Benjamin Autin Aug 29 '18 at 22:51

D'Arcy Rittich · Answer 3 · 2010-08-05T19:58:03.803

24

You can use FLOOR:

select x, ABS(x) - FLOOR(ABS(x))
from (
    select 2.938 as x
) a

Output:

x                                       
-------- ----------
2.938    0.938

Or you can use SUBSTRING:

select x, SUBSTRING(cast(x as varchar(max)), charindex(cast(x as varchar(max)), '.') + 3, len(cast(x as varchar(max))))
from (
    select 2.938 as x
) a

edited Aug 05 '10 at 19:58

answered Aug 05 '10 at 19:53

D'Arcy Rittich

167,292
40
290
283

1

Now try it for negative numbers: `SELECT -0.25 - FLOOR(-0.25)` gives 0.75. – Mark Byers Aug 05 '10 at 19:56
5

Added [ABS](http://msdn.microsoft.com/en-us/library/ms189800%28SQL.90%29.aspx) to handle negative numbers... – OMG Ponies Aug 05 '10 at 19:57
I like converting to a varchar and using the index of . to substr, works for any decimal places. – Brandon Horsley Aug 05 '10 at 20:00

score 8 · Answer 4 · answered Jul 16 '14 at 10:19

More generalized approach may be to merge PARSENAME and % operator. (as answered in two of the answers above)

Results as per 1st approach above by SQLMenace

select PARSENAME(0.001,1)

Result: 001

select PARSENAME(0.0010,1)

Result: 0010

select PARSENAME(-0.001,1)

Result: 001

select PARSENAME(-1,1)

Result: -1 --> Should not return integer part

select PARSENAME(0,1)

Result: 0

select PARSENAME(1,1)

Result: 1 --> Should not return integer part

select PARSENAME(100.00,1)

Result: 00

Results as per 1st approach above by Pavel Morshenyuk "0." is part of result in this case.

SELECT (100.0001 % 1)

Result: 0.0001

SELECT (100.0010 % 1)

Result: 0.0010

SELECT (0.0001 % 1)

Result: 0.0001

SELECT (0001 % 1)

Result: 0

SELECT (1 % 1)

Result: 0

SELECT (100 % 1)

Result: 0

Combining both:

SELECT PARSENAME((100.0001 % 1),1)

Result: 0001

SELECT PARSENAME((100.0010 % 1),1)

Result: 0010

SELECT PARSENAME((0.0001 % 1),1)

Result: 0001

SELECT PARSENAME((0001 % 1),1)

Result: 0

SELECT PARSENAME((1 % 1),1)

Result: 0

SELECT PARSENAME((100 % 1),1)

Result: 0

But still one issue which remains is the zero after the non zero numbers are part of the result (Example: 0.0010 -> 0010). May be one have to apply some other logic to remove that.

score 7 · Answer 5 · answered Aug 05 '10 at 19:54

7

The usual hack (which varies a bit in syntax) is

x - floor(x)

That's the fractional part. To make into an integer, scale it.

(x - floor(x)) * 1000

answered Aug 05 '10 at 19:54

S.Lott

384,516
81
508
779

1

hi think is best solution than the selected – elle0087 Sep 12 '19 at 09:29

score 6 · Answer 6 · answered Nov 22 '19 at 13:08

6

I had the same problem and solved with '%' operator:

select 12.54 % 1;

answered Nov 22 '19 at 13:08

Renato

2,077
1
11
22

Could you explain what the `%` does? – lofihelsinki Nov 22 '19 at 13:25
1

% is the remainder of the euclidean division of one number by another. You can read more [here](https://stackoverflow.com/questions/17524673/understanding-the-modulus-operator) – Renato Nov 22 '19 at 13:49

score 1 · Answer 7 · edited May 23 '13 at 07:51

1

Make it very simple by query:

select substr('123.123',instr('123.123','.')+1, length('123.123')) from dual;

Put your number or column name instead 123.122

edited May 23 '13 at 07:51

Rob

4,927
12
49
54

answered May 23 '13 at 07:26

user2412524

11
1

`from dual` is not SQL server compatible (unless you have table named `dual`, which is usually not the case). You can omit that part however :) – Arvo Nov 22 '19 at 13:13

score 0 · Answer 8 · answered Aug 20 '15 at 08:48

If you want to select only decimal numbers use this WHERE clause:

    (CAST(RIGHT(Myfield, LEN( Myfield)-CHARINDEX('.',Myfield)+1 ) AS FLOAT)) <> 0

If you want a clear list you can sort by decimal/integer:

    CASE WHEN 0 = CAST(RIGHT(Myfield, LEN( Myfield)-CHARINDEX('.',Myfield)+1 ) AS FLOAT) THEN 'Integer' ELSE 'Decimal' END AS Type

score 0 · Answer 9 · answered Aug 05 '10 at 19:55

0

If you know that you want the values to the thousandths, place, it's

SELECT (num - FLOOR(num)) * 1000 FROM table...;

answered Aug 05 '10 at 19:55

Seamus Campbell

17,816
3
52
60

score 0 · Answer 10 · answered Aug 09 '19 at 14:52

0

X - TRUNC(X), works for negatives too.

It would give you the decimal part of the number, as a double, not an integer.

answered Aug 09 '19 at 14:52

MrPmosh

100
12

score 0 · Answer 11 · answered Aug 08 '22 at 13:16

0

It's Gives You Only Decimal Values

Input : select (18.45)%1

OutPut

0.45

answered Aug 08 '22 at 13:16

Ajay

75
5

score -2 · Answer 12 · answered Jun 10 '14 at 18:02

-2

You can use RIGHT :

 select RIGHT(123.45,2) return => 45

answered Jun 10 '14 at 18:02

s michaud

13
1

1

This post is being automatically flagged as low quality because it is so short. Would you mind expanding it by adding some text to explain how this solves the problem? – gung - Reinstate Monica Jun 10 '14 at 18:23
1

Second parameter depends on how many decimals your data type has. – jumxozizi Aug 24 '16 at 11:03

turbobeagle · Answer 13 · 2014-10-22T20:17:35.033

-2

CAST(RIGHT(MyField, LEN( MyField)-CHARINDEX('.',MyField)+1 ) AS FLOAT)

edited Oct 22 '14 at 20:17

answered Oct 22 '14 at 19:36

turbobeagle

1
1

SQL - How do I get only the numbers after the decimal?

13 Answers13

Linked