2

I need to extact fractional part of double in sql, but without "0." For example: number: 5.67 int: 5 fact: 67

Женя Касьян
  • 55
  • 7
  • How many digits? All of them? There can be plenty. A certain number? Then how to round? This is also quite a strange requirement (and as such, can be poorly supported depending on specifics) -- what's the intended use? – ivan_pozdeev Apr 21 '19 at 16:48
  • If this is for money, [you need to use fixed rather than floating point](https://stackoverflow.com/questions/3730019/why-not-use-double-or-float-to-represent-currency/3730040#3730040). – ivan_pozdeev Apr 21 '19 at 16:53
  • @Женя Касьян my answers below have fiddle corrections .Have a check on it!!!! – Nikhil S Oct 28 '20 at 23:22

3 Answers3

1

In most databases, you can use something like this:

select cast((x - floor(x)) * 100 as int)

In SQL Server, you have other options. For instance, to get the fractional part, you can use %:

select (x % 1)

To get two digits:

select floor(100 * (x % 1)) -- or perhaps round()

or:

select (x * 100) % 100
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0
SELECT 5.67 AS Doubles, CAST(5.67  AS INT) AS Fractional_Only, 
PARSENAME(5.67,1) AS DECIMAL_ONLY
Devesh Kumar Singh
  • 20,259
  • 5
  • 21
  • 40
satendra rawat
  • 1
  • 1
-1

this will work in oracle:

select substr(to_char(5.67),instr(to_char(5.67),'.')+1) from dual;

check http://sqlfiddle.com/#!4/e2ac5e/1074

for mssql :

select substring(CONVERT(varchar, 25.65),CHARINDEX('.', CONVERT(varchar, 25.65))+1,5);

http://sqlfiddle.com/#!18/9eecb/41866

check the last one :

select substring(CONVERT(varchar, (15+15)/2.0),
                 CHARINDEX('.', CONVERT(varchar, (15+15)/2.0))+1,1);

http://sqlfiddle.com/#!18/9eecb/41875

Nikhil S
  • 3,786
  • 4
  • 18
  • 32