How to Extract Numeric Characters from MySQL Data for Accurate Comparisons?

Extracting Numeric Characters from MySQL Data for Accurate Comparisons

To compare against specific numerical values in a MySQL table, where database entries may contain non-numeric characters, a reliable method is required to strip out those non-numerals. While PHP functions like preg_replace offer a convenient solution, MySQL requires a native approach.

MySQL Function to Strip Non-Digits

To effectively perform this task, the following MySQL function can be created:

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

Usage

To utilize the STRIP_NON_DIGIT function in a query, simply apply it to the target column as follows:

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

This query will retrieve records where the bar column, after having its non-numeric characters removed, matches the user's input of '12345'.

The above is the detailed content of How to Extract Numeric Characters from MySQL Data for Accurate Comparisons?. For more information, please follow other related articles on the PHP Chinese website!