How Can I Efficiently Strip Non-Numeric Characters from Strings for MySQL Comparisons?

Stripping Non-Numeric Characters in MySQL String Comparison

When comparing strings in MySQL, it's often necessary to remove non-numeric characters that could interfere with the match. To perform this operation, we can't rely on PHP functions like preg_replace directly in MySQL queries.

Instead, we can create a custom MySQL function to strip non-digit characters from a given string. Here's an example function:

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
$$

This function takes a VARCHAR input and iterates through each character, checking if it's a digit. If it is, the character is appended to the output variable.

With this function in place, we can strip non-numeric characters from a string and use it to compare against numeric values in our MySQL query, as follows:

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

The above is the detailed content of How Can I Efficiently Strip Non-Numeric Characters from Strings for MySQL Comparisons?. For more information, please follow other related articles on the PHP Chinese website!