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

How do I get only the numbers afte开发者_Go百科r the decimal?

Example: 2.938 = 938

---

try this:

SELECT (num % 1)

---

one way, works also for negative values

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

select PARSENAME(@1,1)

---

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

---

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.

---

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

---

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

select 12.54 % 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

---

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

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

---

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

---

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

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

---

It's Gives You Only Decimal Values

Input : select (18.45)%1

OutPut

0.45

---

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

---

You can use RIGHT :

 select RIGHT(123.45,2) return => 45