How can I check with a SQL query that a field has a given rounding?



  • What query to check that the values in a column are rounded, for example, to 3 decimal places? Or Inverse query that there are no values other than three-digit rounding.



  • If we mean a table field, then

    SELECT column_name, 
           data_type, 
           numeric_precision, 
           numeric_scale 
    FROM information_schema.columns 
    WHERE table_schema = 'database'
      AND table_name = 'table'
      AND data_type IN ('decimal','float','double');
    

    If we are talking about a field in a certain data set, then in the general case, no way
    How using a query you can parse a string and count the characters after the decimal point

    (LOCATE(SUBSTRING(1.1 FROM 2 FOR 1), field) > 0) * LENGTH(SUBSTRING_INDEX(field, SUBSTRING(1.1 FROM 2 FOR 1), -1))
    

    If you are convinced that tricks with a decimal separator are not expected, then simply do the following:

    (LOCATE('.', field) > 0) * LENGTH(SUBSTRING_INDEX(field, '.', -1))
    

Log in to reply
 

Suggested Topics

  • 2
  • 2
  • 1
  • 3
  • 4