How to do a regular expression replace in MySQL?

Piskvor_19746 0
Viewed 570638

I have a table with ~500k rows; varchar(255) UTF8 column filename contains a file name;

I'm trying to strip out various strange characters out of the filename - thought I'd use a character class: [^a-zA-Z0-9()_ .\-]

Now, is there a function in MySQL that lets you replace through a regular expression? I'm looking for a similar functionality to REPLACE() function - simplified example follows:

SELECT REPLACE('stackowerflow', 'ower', 'over');

Output: "stackoverflow"

/* does something like this exist? */
SELECT X_REG_REPLACE('Stackoverflow','/[A-Zf]/','-'); 

Output: "-tackover-low"

I know about REGEXP/RLIKE, but those only check if there is a match, not what the match is.

(I could do a "SELECT pkey_id,filename FROM foo WHERE filename RLIKE '[^a-zA-Z0-9()_ .\-]'" from a PHP script, do a preg_replace and then "UPDATE foo ... WHERE pkey_id=...", but that looks like a last-resort slow & ugly hack)

mysql-udf mysql regex
13 Answers
user_97020
user_3433020

I think there is an easy way to achieve this and It's working fine for me.

To SELECT rows using REGEX

SELECT * FROM `table_name` WHERE `column_name_to_find` REGEXP 'string-to-find'

To UPDATE rows using REGEX

UPDATE `table_name` SET column_name_to_find=REGEXP_REPLACE(column_name_to_find, 'string-to-find', 'string-to-replace') WHERE column_name_to_find REGEXP 'string-to-find'

REGEXP Reference: https://www.geeksforgeeks.org/mysql-regular-expressions-regexp/

user_81923540

The one below basically finds the first match from the left and then replaces all occurences of it (tested in mysql-5.6).

Usage:

SELECT REGEX_REPLACE('dis ambiguity', 'dis[[:space:]]*ambiguity', 'disambiguity');

Implementation:

DELIMITER $$
CREATE FUNCTION REGEX_REPLACE(
  var_original VARCHAR(1000),
  var_pattern VARCHAR(1000),
  var_replacement VARCHAR(1000)
  ) RETURNS
    VARCHAR(1000)
  COMMENT 'Based on https://techras.wordpress.com/2011/06/02/regex-replace-for-mysql/'
BEGIN
  DECLARE var_replaced VARCHAR(1000) DEFAULT var_original;
  DECLARE var_leftmost_match VARCHAR(1000) DEFAULT
    REGEX_CAPTURE_LEFTMOST(var_original, var_pattern);
    WHILE var_leftmost_match IS NOT NULL DO
      IF var_replacement <> var_leftmost_match THEN
        SET var_replaced = REPLACE(var_replaced, var_leftmost_match, var_replacement);
        SET var_leftmost_match = REGEX_CAPTURE_LEFTMOST(var_replaced, var_pattern);
        ELSE
          SET var_leftmost_match = NULL;
        END IF;
      END WHILE;
  RETURN var_replaced;
END $$
DELIMITER ;

DELIMITER $$
CREATE FUNCTION REGEX_CAPTURE_LEFTMOST(
  var_original VARCHAR(1000),
  var_pattern VARCHAR(1000)
  ) RETURNS
    VARCHAR(1000)
  COMMENT '
  Captures the leftmost substring that matches the [var_pattern]
  IN [var_original], OR NULL if no match.
  '
BEGIN
  DECLARE var_temp_l VARCHAR(1000);
  DECLARE var_temp_r VARCHAR(1000);
  DECLARE var_left_trim_index INT;
  DECLARE var_right_trim_index INT;
  SET var_left_trim_index = 1;
  SET var_right_trim_index = 1;
  SET var_temp_l = '';
  SET var_temp_r = '';
  WHILE (CHAR_LENGTH(var_original) >= var_left_trim_index) DO
    SET var_temp_l = LEFT(var_original, var_left_trim_index);
    IF var_temp_l REGEXP var_pattern THEN
      WHILE (CHAR_LENGTH(var_temp_l) >= var_right_trim_index) DO
        SET var_temp_r = RIGHT(var_temp_l, var_right_trim_index);
        IF var_temp_r REGEXP var_pattern THEN
          RETURN var_temp_r;
          END IF;
        SET var_right_trim_index = var_right_trim_index + 1;
        END WHILE;
      END IF;
    SET var_left_trim_index = var_left_trim_index + 1;
    END WHILE;
  RETURN NULL;
END $$
DELIMITER ;
user_70328560

Yes, you can.

UPDATE table_name 
  SET column_name = 'seach_str_name'
  WHERE column_name REGEXP '[^a-zA-Z0-9()_ .\-]';
user_87810110
Related