How to Efficiently Remove Non-Alphanumeric Characters from Strings in MySQL?

Efficiently Removing Non-Alphanumeric Characters from Strings in MySQL

In MySQL, comparing strings can be a performance-intensive task. To optimize efficiency, it's often beneficial to remove non-alphanumeric characters before performing the comparison. Let's explore different approaches to accomplish this, including leveraging regex functionality and creating a custom function.

MySQL 8.0 and Higher: Utilizing Regex

With MySQL 8.0 or later versions, you can employ regex (regular expressions) to replace non-alphanumeric characters. The following SQL statement demonstrates this approach:

UPDATE {table} SET {column} = REGEXP_REPLACE({column}, '[^0-9a-zÀ-ÿ ]', '')

In this statement, the regex [^0-9a-zÀ-ÿ ] matches any character that is not a number, letter, space, or accented character.

MySQL 5.7 and Lower: Creating a Custom Function

For earlier MySQL versions (5.7 or lower), regex functionality is unavailable. As an alternative, you can create a custom function that strips non-alphanumeric characters. Here's an example implementation:

DROP FUNCTION IF EXISTS alphanum;
DELIMITER |
CREATE FUNCTION alphanum(str CHAR(255)) RETURNS CHAR(255) DETERMINISTIC
BEGIN
  DECLARE i, len SMALLINT DEFAULT 1;
  DECLARE ret CHAR(255) DEFAULT '';
  DECLARE c CHAR(1);
  IF str IS NOT NULL THEN
    SET len = CHAR_LENGTH(str);
    REPEAT
      BEGIN
        SET c = MID(str, i, 1);
        IF c REGEXP '[[:alnum:]]' THEN
          SET ret=CONCAT(ret,c);
        END IF;
        SET i = i + 1;
      END;
    UNTIL i > len END REPEAT;
  ELSE
    SET ret='';
  END IF;
  RETURN ret;
END |
DELIMITER ;

Once created, you can use this function to strip non-alphanumeric characters, as shown below:

select 'This works finally!', alphanum('This works finally!');

The above is the detailed content of How to Efficiently Remove Non-Alphanumeric Characters from Strings in MySQL?. For more information, please follow other related articles on the PHP Chinese website!