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À-ÿ ]', '')
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 ;
Using this function, you can perform character filtering as follows:
select 'This works finally!', alphanum('This works finally!');
Output:
+---------------------+---------------------------------+ | This works finally! | alphanum('This works finally!') | +---------------------+---------------------------------+ | This works finally! | Thisworksfinally | +---------------------+---------------------------------+
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!