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

Stripping Non-Alphanumeric Characters from Strings in MySQL

Problem:

When comparing strings in MySQL, efficiency can be improved by removing all non-alphanumeric characters. Currently, multiple REPLACE functions are being employed, but a more efficient and elegant solution is sought.

Solution:

For MySQL 8.0 or higher:

MySQL now supports Regex replacement, making it possible to remove non-alphanumeric characters with a single statement:

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

For MySQL 5.7 or lower:

Regex support is unavailable. A custom function, 'alphanum,' can be created to perform the character stripping:

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 ;

This function can then be used to strip non-alphanumeric characters from strings:

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

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