Home > Database > Mysql Tutorial > How to Efficiently Remove Non-Alphanumeric Characters from Strings in MySQL?

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

Barbara Streisand
Release: 2024-12-01 14:18:13
Original
652 people have browsed it

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À-ÿ ]', '')
Copy after login

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 ;
Copy after login

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

select 'This works finally!', alphanum('This works finally!');
Copy after login

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!

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
Latest Articles by Author
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template