How to remove all non-alpha numeric characters from a string in MySQL?

by

Using MySQL 8.0 or higher

Courtesy of michal.jakubeczy’s answer below, replacing by Regex is now supported by MySQL:

UPDATE {table} SET {column} = REGEXP_REPLACE({column}, '[^0-9a-zA-Z ]', '')

Using MySQL 5.7 or lower

Regex isn’t supported here. I had to create my own function called alphanum which stripped the chars for me:

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 ;

Now I can do:

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

and I get:

+---------------------+---------------------------------+
| This works finally! | alphanum('This works finally!') |
+---------------------+---------------------------------+
| This works finally! | Thisworksfinally                |
+---------------------+---------------------------------+
1 row in set (0.00 sec)

Hurray!

More Related Contents:

  1. How to do a regular expression replace in MySQL?
  2. How to convert a string to date in MySQL?
  3. How to find similar results and sort by similarity?
  4. MySQL Split Comma Separated String Into Temp Table
  5. MySQL strip non-numeric characters to compare
  6. How do I do a fuzzy match of company names in MYSQL with PHP for auto-complete?
  7. How do you extract a numerical value from a string in a MySQL query?
  8. SQL order string as number
  9. How to extract two consecutive digits from a text field in MySQL?
  10. How to extract the nth word and count word occurrences in a MySQL string?
  11. How to escape single quotes in MySQL
  12. How to count words in MySQL / regular expression replacer?
  13. MySQL – length() vs char_length()
  14. #1139 – Got error ‘repetition-operator operand invalid’ from regexp
  15. Capitalize first letter. MySQL
  16. Can MySQL replace multiple characters?
  17. How to fix double-encoded UTF8 characters (in an utf-8 table)
  18. Does MySQL Regexp support Unicode matching
  19. MySql – Way to update portion of a string?
  20. How do I refer to capture groups in a MySQL regex?
  21. MySQL SELECT LIKE or REGEXP to match multiple words in one record
  22. mysql: why comparing a ‘string’ to 0 gives true?
  23. Is it Possible to Enforce Data Checking in MySQL using Regular expression
  24. How to regex in a MySQL query
  25. MySQL : how to remove double or more spaces from a string?
  26. remove all numeric characters from column mysql
  27. Strip last two characters of a column in MySQL
  28. MySql Query Replace NULL with Empty String in Select
  29. Last index of a given substring in MySQL
  30. mySQL regex in the where clause

Leave a Comment