I would like to query the integer part and fractional part of all decimal(8,4) values in a column. By that I mean, I want to know the actual given values, and not the max allowed by the data type, which are integer = 4, fractional = 4.

For example, 33.99 would return (2,2) and 1.375 would return (1,3). I have started trying to parse the numbers as strings using char_length() like this:

SELECT max(char_length(SUBSTRING_INDEX(col,'.',1))), max(char_length(SUBSTRING_INDEX(col,'.',-1)));

A similar, more intuitive, and uglier way in Oracle is

SELECT max(length(regexp_substr(col,'^.*[.]',1))), max(length(regexp_substr(col,'[.].*$',1)))

But is there a better way that exploits MySQL's knowledge that these are actually numbers?

This page implies that the true lengths of numbers - e.g. without leading 0s - is known and retrievable, so I was hoping there was a function that would just do this. Does this have something to do with the assertion that "MySQL returns all data as strings and expects you to convert it yourself" or is that specific to that Python module?

  • Should 0.275 return (0, 3) or (1, 3)? – ypercubeᵀᴹ May 26 '15 at 14:14
  • (0,3) would be more informative. – WAF May 26 '15 at 16:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.