How do I get only the numbers after the decimal?
Example: 2.938 = 938
This question is related to
sql
sql-server
tsql
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
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
try this:
SELECT (num % 1)
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.
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
X - TRUNC(X), works for negatives too.
It would give you the decimal part of the number, as a double, not an integer.
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
If you know that you want the values to the thousandths, place, it's
SELECT (num - FLOOR(num)) * 1000 FROM table...;
I had the same problem and solved with '%' operator:
select 12.54 % 1;
CAST(RIGHT(MyField, LEN( MyField)-CHARINDEX('.',MyField)+1 ) AS FLOAT)
You can use RIGHT :
select RIGHT(123.45,2) return => 45
Source: Stackoverflow.com