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

User · Answer

You can use RIGHT     select RIGHT 123 45 2  return   gt  45

User · Answer

X - TRUNC X   works for negatives too   It would give you the decimal part of the number  as a double  not an integer

User · Answer

I had the same problem and solved with     operator   select 12 54   1

User · Answer

one way  works also for negative values  declare  1 decimal 4 3  select  1   2 938  select PARSENAME  1 1

User · Answer

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

User · Answer

try this   SELECT  num   1

User · Answer

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

User · Answer

If you want to select only decimal numbers use this WHERE clause        CAST RIGHT Myfield  LEN  Myfield -CHARINDEX     Myfield  1   AS FLOAT    lt  gt  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

User · Answer

CAST RIGHT MyField   LEN  MyField -CHARINDEX     MyField  1   AS FLOAT

User · Answer

If you know that you want the values to the thousandths  place  it s  SELECT  num - FLOOR num     1000 FROM table

User · Answer

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

User · Answer

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

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

Examples related to sql

Examples related to sql-server

Examples related to tsql