How to Efficiently Compare Numeric Data with Non-Numeric Characters in MySQL?

Stripping Non-Numeric Characters in MySQL to Facilitate Comparisons

To find matching records based on numeric input from users, it's necessary to account for non-numeric characters in database values. This article explores a MySQL function to strip these non-numeric characters and facilitate accurate comparisons.

Problem Statement

Given a table with a field containing numbers potentially interspersed with non-numeric characters, the task is to find records that match a specific numeric input entered by the user.

MySQL Function: STRIP_NON_DIGIT

To address this problem, a MySQL function called STRIP_NON_DIGIT can be created:

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

Working Example

To use the STRIP_NON_DIGIT function, execute SQL queries like the following:

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

This query will return records from the 'foo' table where the value in the 'bar' field stripped of non-numeric characters matches '12345' entered by the user.

Benefits

This function provides a straightforward and efficient solution to the problem of comparing numeric values with non-numeric characters in MySQL databases. It eliminates the need for complex regular expressions or PHP functions and is easy to implement.

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