Home > Database > Mysql Tutorial > How Can I Efficiently Filter Non-Alphanumeric Characters from MySQL Strings?

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!

source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template