How to Compare Numeric Values in MySQL Despite Non-Numeric Characters?

MySQL: Stripping Non-Numeric Characters for Comparison

In MySQL, you can filter records based on a specific number that a user enters, even if the stored numerical value in the database differs due to unwanted non-numeric characters. To achieve this, consider using the following approach:

The STRIP_NON_DIGIT function provided below removes non-digit characters from an input string, enabling straightforward comparison of numerical values:

DROP FUNCTION IF EXISTS STRIP_NON_DIGIT;
DELIMITER $$
CREATE FUNCTION STRIP_NON_DIGIT(input VARCHAR(255))
   RETURNS VARCHAR(255)
BEGIN
   DECLARE output   VARCHAR(255) DEFAULT '';
   DECLARE iterator INT          DEFAULT 1;
   WHILE iterator < (LENGTH(input) + 1) DO
      IF SUBSTRING(input, iterator, 1) IN ( '0', '1', '2', '3', '4', '5', '6', '7', '8', '9' ) THEN
         SET output = CONCAT(output, SUBSTRING(input, iterator, 1));
      END IF;
      SET iterator = iterator + 1;
   END WHILE;
   RETURN output;
END
$$

With this function, you can filter records as follows:

SELECT * FROM foo WHERE STRIP_NON_DIGIT(bar) = '12345'

This query will retrieve records where the numerical value in the 'bar' column matches '12345', even if the stored value in the database includes non-numeric characters such as '123zz4-5'.

The above is the detailed content of How to Compare Numeric Values in MySQL Despite Non-Numeric Characters?. For more information, please follow other related articles on the PHP Chinese website!