CODEWITHSUNDEEP
mysql
user1538524
user1538524

Reputation: 49

Store only numbers after Comma mysql

How to save only the numbers after the comma with mysql functions?

eg : 16.03 ----> 03

Upvotes: 1

Views: 1854

Answers (3)

Carsten Massmann
Carsten Massmann

Reputation: 28196

Or simply:

14.03 % 1

--> 0.03

Also works for negative numbers:

-14.03 % 1

--> -0.03

If you are after exactly the two digits after the decimal point (and you are dealing with positive numbers only) you can do the following:

substring(format( num %1,2),3,2)  -- num being the column (integer of float, double ...)
--> 03  -- for num=14.03
--> 00  -- for num=14
--> 05  -- for num=14.046739

Of course, if you want all digits, you could leave out the format()

substring(num %1,3,2)
--> 03      -- for num=14.03
-->         -- for num=14
--> 046739  -- for num=14.046739

but that makes the integer case quite ugly.

Upvotes: 0

Sylvain Leroux
Sylvain Leroux

Reputation: 51990

If your values come from a table, you will have to be prepared to deal with the fact that some value could be integers (i.e.: having no decimal separator while converted to a string):

SELECT IF(LOCATE('.', value), SUBSTRING_INDEX(value,'.', -1), "0") FROM tbl;

See http://sqlfiddle.com/#!2/062c8/11

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269633

If I understand correctly, you want the fractional part of the number. In MySQL, you can do this with mod():

select mod(14.03, 1)

Yields "0.03".

EDIT:

Juhana makes a very good point. MySQL freely converts between numbers and strings, so you can use substr() on this:

select substr(14.03, locate('.', 14.03)+1)

If you want the digits after a comma, replace '.' with ','.

Upvotes: 3

Related Questions