7

I have following number in SQL Server 2008 : 5.4564556
I just want to get only 4564556 from 5.4564556
it would be better if i can get only 4 among 4564556 that is first digit of fractional part..

How can i do it?

Thanks

Dor Cohen
  • 16,769
  • 23
  • 93
  • 161
ghanshyam.mirani
  • 3,075
  • 11
  • 45
  • 85

5 Answers5

12

You can try in this way : 

SELECT (5.4564556 % 1)
aleroot
  • 71,077
  • 30
  • 176
  • 213
  • % operator in t-sql works for decimal(p,s), and it does not work for float. `SELECT (5.4564556e0 % 1)` results in `Msg 402, Level 16, State 1, Line 26 The data types float and int are incompatible in the modulo operator.` – Mx.Wolf Mar 28 '22 at 11:05
11

You can also use FLOOR():

SELECT Value - FLOOR(Value)

In your case:

5.4564556 - 5 = 0.4564556

in case of negative value you can use ABS

SELECT ABS(Value) - ABS(FLOOR(Value))
Dor Cohen
  • 16,769
  • 23
  • 93
  • 161
  • This is actually the best answer, because it is mathematically correct, and thus works for the set of all floats. – chthon Jan 16 '20 at 07:52
5

To get all the numbers behind the '.', you can (ab)use PARSENAME

select PARSENAME(5.4564556,1)

returns 4564556

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

Community
  • 1
  • 1
StuartLC
  • 104,537
  • 17
  • 209
  • 285
  • @ghanshyam.mirani Note that PARSENAME returns a `NVARCHAR(256)`. I also didn't see the second part of your question `select LEFT(PARSENAME('5.4564556', 1), 1)` will return just the 4. You may need to cast it back to numeric type. – StuartLC Aug 26 '12 at 10:24
2

Try this

 select cast(5.4564556 % 1 * 10 as int)
FJT
  • 1,923
  • 1
  • 15
  • 14
2

you can get first four decimal parts by : 

select SUBSTRING (PARSENAME(5.4564556,1), 1, 4)
NG.
  • 5,695
  • 2
  • 19
  • 30