How Can I Efficiently Filter Non-Alphanumeric Characters from MySQL Strings?-Mysql Tutorial-php.cn

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

DDD

Release： 2024-12-07 20:28:14

Original

272 people have browsed it

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

Efficient Character Filtering in MySQL Strings

When comparing strings for efficiency, it's crucial to eliminate non-alpha numeric characters. Instead of employing multiple REPLACE functions, consider using alternative solutions.

MySQL 8.0 and Above

MySQL 8.0 supports regular expression replacement, allowing you to replace non-alphanumeric characters with an empty string:

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

Copy after login

MySQL 5.7 and Below

As regular expressions are not supported in MySQL 5.7, you can create a custom function such as the following:

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 ;

Copy after login

Using this function, you can perform character filtering as follows:

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

Copy after login

Output:

+---------------------+---------------------------------+
| This works finally! | alphanum('This works finally!') |
+---------------------+---------------------------------+
| This works finally! | Thisworksfinally                |
+---------------------+---------------------------------+

Copy after login

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