0

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?

2
  • Should 0.275 return (0, 3) or (1, 3)? May 26, 2015 at 14:14
  • (0,3) would be more informative.
    – WAF
    May 26, 2015 at 16:26

1 Answer 1

0

For MySQL:

SELECT NUMERIC_PRECISION, NUMERIC_SCALE FROM information_schema.columns 
WHERE table_name= 'table' AND column_name = 'column' 

Response in HeidiSQL

Response in HeidSQL

1
  • Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center.
    – Community Bot
    Feb 23, 2022 at 11:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.