如何在 MySQL 中替换一个正则表达式?

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)

转载于:https://stackoverflow.com/questions/986826/how-to-do-a-regular-expression-replace-in-mysql

weixin_41568127
?yb? Found this library from another SO thread: github.com/mysqludf/lib_mysqludf_preg works perfectly.
接近 3 年之前 回复
csdnceshi76
斗士狗 IMO, selecting, using PHP, and then updating mysql is not such a bad "hack". I do prefer using MySQL native methods when possible - supposedly it's faster. But I have a system that uses a lot of PHP and it does hundreds of thousands of such replacements in a few seconds (the system I built is designed for exactly this kind of work).
大约 4 年之前 回复
csdnceshi75
衫裤跑路 I've created regexp_split (function + procedure) & regexp_replace, which are implemented with REGEXP operator. For simple lookups, it will do the trick. You may find it here - so, this is the way with MySQL stored code, no UDF. If you'll find some bugs, which are not covered by known limitations - feel free to open the issue.
6 年多之前 回复
weixin_41568131
10.24 Good to know, thanks; I'll edit this into the question.
6 年多之前 回复
csdnceshi53
Lotus@ Related, simpler, version of this question: stackoverflow.com/questions/6942973/…
6 年多之前 回复
weixin_41568131
10.24 I have done that...in 2009, when I was looking around for it. Since there has been zero progress on it - apparently it's not such an important feature. (btw Postgres has it: stackoverflow.com/questions/11722995/… )
6 年多之前 回复
csdnceshi79
python小菜 It's a feature request since 2007: bugs.mysql.com/bug.php?id=27389. If you really want this feature, log in and click "Affects me" button. Hopefully it will get enough votes.
6 年多之前 回复

10个回答

MySQL 8.0+ you could use natively REGEXP_REPLACE.

12.5.2 Regular Expressions:

REGEXP_REPLACE(expr, pat, repl[, pos[, occurrence[, match_type]]])

Replaces occurrences in the string expr that match the regular expression specified by the pattern pat with the replacement string repl, and returns the resulting string. If expr, pat, or repl is NULL, the return value is NULL.

and Regular expression support:

Previously, MySQL used the Henry Spencer regular expression library to support regular expression operators (REGEXP, RLIKE).

Regular expression support has been reimplemented using International Components for Unicode (ICU), which provides full Unicode support and is multibyte safe. The REGEXP_LIKE() function performs regular expression matching in the manner of the REGEXP and RLIKE operators, which now are synonyms for that function. In addition, the REGEXP_INSTR(), REGEXP_REPLACE(), and REGEXP_SUBSTR() functions are available to find match positions and perform substring substitution and extraction, respectively.

SELECT REGEXP_REPLACE('Stackoverflow','[A-Zf]','-',1,0,'c'); 
-- Output:
-tackover-low

DBFiddle Demo

Use MariaDB instead. It has a function

REGEXP_REPLACE(col, regexp, replace)

See MariaDB docs and PCRE Regular expression enhancements

Note that you can use regexp grouping as well (I found that very useful):

SELECT REGEXP_REPLACE("stackoverflow", "(stack)(over)(flow)", '\\2 - \\1 - \\3')

returns

over - stack - flow
weixin_41568196
撒拉嘿哟木头 MySQL 8.0 supports it too.
2 年多之前 回复
weixin_41568174
from.. MariaDB is a drop-in replacement for MySQL. So it is no "change of platform" but more like choosing a different airline for the same trip
接近 3 年之前 回复
csdnceshi74
7*4 Changing an entire platform is hardly a realistic solution.
接近 3 年之前 回复
csdnceshi77
狐狸.fox For the next time I need it, here's syntax for changing a whole column: UPDATE table SET Name = REGEXP_REPLACE(Name, "-2$", "\\1") This removes -2 from abcxyz-2 from a whole column at once.
大约 4 年之前 回复
weixin_41568184
叼花硬汉 Benni--thanks for pointing this out, but I'm a little confused on the actual implementation... mind chiming in on my question over here? stackoverflow.com/questions/27498929/… cc @Piskvor
接近 6 年之前 回复
csdnceshi64
游.程 this is from mariadb 10
大约 6 年之前 回复
weixin_41568127
?yb? Niiiice! Even more so because we have already migrated to it for unrelated reasons. Thanks for the tip :)
大约 6 年之前 回复

My brute force method to get this to work was just:

  1. Dump the table - mysqldump -u user -p database table > dump.sql
  2. Find and replace a couple patterns - find /path/to/dump.sql -type f -exec sed -i 's/old_string/new_string/g' {} \;, There are obviously other perl regeular expressions you could perform on the file as well.
  3. Import the table - mysqlimport -u user -p database table < dump.sql

If you want to make sure the string isn't elsewhere in your dataset, run a few regular expressions to make sure they all occur in a similar environment. It's also not that tough to create a backup before you run a replace, in case you accidentally destroy something that loses depth of information.

csdnceshi60
℡Wang Yan One more confirmation: I enjoyed this method, until I started to get errors about the number of columns not maching the expected value ( = broken the whole db / table).
大约 2 年之前 回复
weixin_41568184
叼花硬汉 Years late to the answer @speshak but the reason I chose to access the file like this was because I originally very nervous for the same reasons as mentioned above. At the time it seemed like separating the "find the file" part from the "replace" part would make the code easier to read before I submitted it
2 年多之前 回复
csdnceshi72
谁还没个明天 Having used this method in the past, I aggre with Raul, this is very risky. You need to be absolutely certain as well, that your string is not elswhere in your dataset.
5 年多之前 回复
csdnceshi76
斗士狗 Very risky, and unpractical with big data sets, or with referential integrity in place: for remove the data and then insert it again you will have to turn referential integrity off, leaving in practice your database off also.
6 年多之前 回复
csdnceshi69
YaoRaoLov can work if the the replace can't broke the SQL itself.
8 年多之前 回复
weixin_41568134
MAO-EYE Seems strange to me that you'd use find like that, I would shorten the command to sed -i 's/old_string/new_string/g' /path/to/dump.sql
8 年多之前 回复
csdnceshi68
local-host Okay, that should work, too; I didn't consider an offline replace. Nice out-of-the-box thinking there!
8 年多之前 回复

No.

But if you have access to your server, you could use a user defined function (UDF) like mysql-udf-regexp.

EDIT: MySQL 8.0+ you could use natively REGEXP_REPLACE. More in answer above

csdnceshi76
斗士狗 you should try out the lib_mysqludf_preg library below as it works great. This the verbose version as it returns a blob by default and I don't know if you have a multibyte charset as your default: select cast( T.R as char) COLLATE utf8_unicode_ci from (select preg_replace('/ä/', '', 'öõüä') R ) T
6 年多之前 回复
csdnceshi58
Didn"t forge Windows users: The UDF Library linked here doesn't seem to have good windows support. The windows installation method outlined did not work well for me.
接近 7 年之前 回复
weixin_41568134
MAO-EYE MySQL itself does not support multi-byte characters with its RegEx features.
7 年多之前 回复
csdnceshi59
ℙℕℤℝ Well this was a year ago, but I'm pretty sure I identified the issue correctly at the time. No idea if they've made any progress, since I haven't touched this tool since.
7 年多之前 回复
csdnceshi80
胖鸭 Really? that thing sounds like a security leak since that "long numeric string" looks like some buffer overrun.
7 年多之前 回复
csdnceshi59
ℙℕℤℝ Unfortunately mysql-udf-regexp doesn't seem to have support for multibyte characters. regexp_replace('äöõü', 'ä', '') returns a long numeric string instead of real text.
8 年多之前 回复
weixin_41568131
10.24 Mysql does not have that feature built-in. I'm told that Oracle has that (no help for you though)
10 年多之前 回复
csdnceshi79
python小菜 REGEXP_REPLACE as a User Defined Function? Looks promising, will look into it. Thanks!
11 年多之前 回复

I'm happy to report that since this question was asked, now there is a satisfactory answer! Take a look at this terrific package:

https://github.com/mysqludf/lib_mysqludf_preg

Sample SQL:

SELECT PREG_REPLACE('/(.*?)(fox)/' , 'dog' , 'the quick brown fox' ) AS demo;

I found the package from this blog post as linked on this question.

csdnceshi76
斗士狗 that would be also my question!
大约 4 年之前 回复
csdnceshi78
程序go how would you update a value in a table?
4 年多之前 回复

we solve this problem without using regex this query replace only exact match string.

update employee set
employee_firstname = 
trim(REPLACE(concat(" ",employee_firstname," "),' jay ',' abc '))

Example:

emp_id employee_firstname

1 jay

2 jay ajay

3 jay

After executing query result:

emp_id employee_firstname

1 abc

2 abc ajay

3 abc

csdnceshi57
perhaps? Looks like a pretty solid workaround for word replacement!
大约 3 年之前 回复
csdnceshi54
hurriedly% He's padding the employeename with spaces before and after. This allows him to search-replace for (space)employeename(space), which avoids catching the employeename "jay" if its part of a larger string "ajay." Then he trims the spaces out when done.
4 年多之前 回复
csdnceshi51
旧行李 what are the two pairs of double quotes for?
4 年多之前 回复
weixin_41568131
10.24 I have no idea why this answer had no votes but this is works perfectly.
接近 5 年之前 回复

UPDATE 2: A useful set of regex functions including REGEXP_REPLACE have now been provided in MySQL 8.0. This renders reading on unnecessary unless you're constrained to using an earlier version.


UPDATE 1: Have now made this into a blog post: http://stevettt.blogspot.co.uk/2018/02/a-mysql-regular-expression-replace.html


The following expands upon the function provided by Rasika Godawatte but trawls through all necessary substrings rather than just testing single characters:

-- ------------------------------------------------------------------------------------
-- USAGE
-- ------------------------------------------------------------------------------------
-- SELECT reg_replace(<subject>,
--                    <pattern>,
--                    <replacement>,
--                    <greedy>,
--                    <minMatchLen>,
--                    <maxMatchLen>);
-- where:
-- <subject> is the string to look in for doing the replacements
-- <pattern> is the regular expression to match against
-- <replacement> is the replacement string
-- <greedy> is TRUE for greedy matching or FALSE for non-greedy matching
-- <minMatchLen> specifies the minimum match length
-- <maxMatchLen> specifies the maximum match length
-- (minMatchLen and maxMatchLen are used to improve efficiency but are
--  optional and can be set to 0 or NULL if not known/required)
-- Example:
-- SELECT reg_replace(txt, '^[Tt][^ ]* ', 'a', TRUE, 2, 0) FROM tbl;
DROP FUNCTION IF EXISTS reg_replace;
DELIMITER //
CREATE FUNCTION reg_replace(subject VARCHAR(21845), pattern VARCHAR(21845),
  replacement VARCHAR(21845), greedy BOOLEAN, minMatchLen INT, maxMatchLen INT)
RETURNS VARCHAR(21845) DETERMINISTIC BEGIN 
  DECLARE result, subStr, usePattern VARCHAR(21845); 
  DECLARE startPos, prevStartPos, startInc, len, lenInc INT;
  IF subject REGEXP pattern THEN
    SET result = '';
    -- Sanitize input parameter values
    SET minMatchLen = IF(minMatchLen < 1, 1, minMatchLen);
    SET maxMatchLen = IF(maxMatchLen < 1 OR maxMatchLen > CHAR_LENGTH(subject),
                         CHAR_LENGTH(subject), maxMatchLen);
    -- Set the pattern to use to match an entire string rather than part of a string
    SET usePattern = IF (LEFT(pattern, 1) = '^', pattern, CONCAT('^', pattern));
    SET usePattern = IF (RIGHT(pattern, 1) = '$', usePattern, CONCAT(usePattern, '$'));
    -- Set start position to 1 if pattern starts with ^ or doesn't end with $.
    IF LEFT(pattern, 1) = '^' OR RIGHT(pattern, 1) <> '$' THEN
      SET startPos = 1, startInc = 1;
    -- Otherwise (i.e. pattern ends with $ but doesn't start with ^): Set start pos
    -- to the min or max match length from the end (depending on "greedy" flag).
    ELSEIF greedy THEN
      SET startPos = CHAR_LENGTH(subject) - maxMatchLen + 1, startInc = 1;
    ELSE
      SET startPos = CHAR_LENGTH(subject) - minMatchLen + 1, startInc = -1;
    END IF;
    WHILE startPos >= 1 AND startPos <= CHAR_LENGTH(subject)
      AND startPos + minMatchLen - 1 <= CHAR_LENGTH(subject)
      AND !(LEFT(pattern, 1) = '^' AND startPos <> 1)
      AND !(RIGHT(pattern, 1) = '$'
            AND startPos + maxMatchLen - 1 < CHAR_LENGTH(subject)) DO
      -- Set start length to maximum if matching greedily or pattern ends with $.
      -- Otherwise set starting length to the minimum match length.
      IF greedy OR RIGHT(pattern, 1) = '$' THEN
        SET len = LEAST(CHAR_LENGTH(subject) - startPos + 1, maxMatchLen), lenInc = -1;
      ELSE
        SET len = minMatchLen, lenInc = 1;
      END IF;
      SET prevStartPos = startPos;
      lenLoop: WHILE len >= 1 AND len <= maxMatchLen
                 AND startPos + len - 1 <= CHAR_LENGTH(subject)
                 AND !(RIGHT(pattern, 1) = '$' 
                       AND startPos + len - 1 <> CHAR_LENGTH(subject)) DO
        SET subStr = SUBSTRING(subject, startPos, len);
        IF subStr REGEXP usePattern THEN
          SET result = IF(startInc = 1,
                          CONCAT(result, replacement), CONCAT(replacement, result));
          SET startPos = startPos + startInc * len;
          LEAVE lenLoop;
        END IF;
        SET len = len + lenInc;
      END WHILE;
      IF (startPos = prevStartPos) THEN
        SET result = IF(startInc = 1, CONCAT(result, SUBSTRING(subject, startPos, 1)),
                        CONCAT(SUBSTRING(subject, startPos, 1), result));
        SET startPos = startPos + startInc;
      END IF;
    END WHILE;
    IF startInc = 1 AND startPos <= CHAR_LENGTH(subject) THEN
      SET result = CONCAT(result, RIGHT(subject, CHAR_LENGTH(subject) + 1 - startPos));
    ELSEIF startInc = -1 AND startPos >= 1 THEN
      SET result = CONCAT(LEFT(subject, startPos), result);
    END IF;
  ELSE
    SET result = subject;
  END IF;
  RETURN result;
END//
DELIMITER ;

Demo

Rextester Demo

Limitations

  1. This method is of course going to take a while when the subject string is large. Update: Have now added minimum and maximum match length parameters for improved efficiency when these are known (zero = unknown/unlimited).
  2. It won't allow substitution of backreferences (e.g. \1, \2 etc.) to replace capturing groups. If this functionality is needed, please see this answer which attempts to provide a workaround by updating the function to allow a secondary find and replace within each found match (at the expense of increased complexity).
  3. If ^and/or $ is used in the pattern, they must be at the very start and very end respectively - e.g. patterns such as (^start|end$) are not supported.
  4. There is a "greedy" flag to specify whether the overall matching should be greedy or non-greedy. Combining greedy and lazy matching within a single regular expression (e.g. a.*?b.*) is not supported.

Usage Examples

The function has been used to answer the following StackOverflow questions:

csdnceshi67
bug^君 Wow! Is perfect!
大约 2 年之前 回复

You 'can' do it ... but it's not very wise ... this is about as daring as I'll try ... as far as full RegEx support your much better off using perl or the like.

UPDATE db.tbl
SET column = 
CASE 
WHEN column REGEXP '[[:<:]]WORD_TO_REPLACE[[:>:]]' 
THEN REPLACE(column,'WORD_TO_REPLACE','REPLACEMENT')
END 
WHERE column REGEXP '[[:<:]]WORD_TO_REPLACE[[:>:]]'
weixin_41568184
叼花硬汉 ;-) As such with 'Should and Shall...'
大约 7 年之前 回复
weixin_41568110
七度&光 I guess there's a difference between "not wise" and "incorrect".
大约 7 年之前 回复
weixin_41568184
叼花硬汉 "Sigh ... It would have wiser to simply not answer this one :-)"
7 年多之前 回复
csdnceshi57
perhaps? I didn't down-vote you, but any answer that says "This is a terrible idea and you definitely should not do this, but here it is: ..." is probably going to get down voted :-).
7 年多之前 回复
weixin_41568184
叼花硬汉 Err ... I'm losing some points here ... Please explain why I'm getting downvoted when I clearly stated this as "NOT VERY WISE"
7 年多之前 回复
weixin_41568184
叼花硬汉 Actually Ryan in this case you're incorrect as the markers will only find matches for the zero-length word 'boundaries' so only words with boundaries before and after the word would match ... It's still a bad idea though ...
大约 8 年之前 回复
weixin_41568184
叼花硬汉 ... that's exactly why I stated that it wasn't very wise ... in the use case you provide this would most definitely fail. In short it's a bad idea to use 'regex-like' structure. Even worse ... if you drop the where clause all your values will be NULL ...
大约 8 年之前 回复
csdnceshi57
perhaps? No, that won't work. Imagine your column contains 'asdfWORD_TO_REPLACE WORD_TO_REPLACE". Your method would result in 'asdfREPLACEMENT REPLACEMENT" where the correct answer would be "asdfWORD_TO_REPLACE REPLACEMENT".
大约 8 年之前 回复

We can use IF condition in SELECT query as below:

Suppose that for anything with "ABC","ABC1","ABC2","ABC3",..., we want to replace with "ABC" then using REGEXP and IF() condition in the SELECT query, we can achieve this.

Syntax:

SELECT IF(column_name REGEXP 'ABC[0-9]$','ABC',column_name)
FROM table1 
WHERE column_name LIKE 'ABC%';

Example:

SELECT IF('ABC1' REGEXP 'ABC[0-9]$','ABC','ABC1');
weixin_41568110
七度&光 Hello, thank you for the suggestion. I have been trying something similar, but the performance on my data sets has been unsatisfactory. For smallish sets, this may be viable.
接近 6 年之前 回复

I recently wrote a MySQL function to replace strings using regular expressions. You could find my post at the following location:

http://techras.wordpress.com/2011/06/02/regex-replace-for-mysql/

Here is the function code:

DELIMITER $$

CREATE FUNCTION  `regex_replace`(pattern VARCHAR(1000),replacement VARCHAR(1000),original VARCHAR(1000))
RETURNS VARCHAR(1000)
DETERMINISTIC
BEGIN 
 DECLARE temp VARCHAR(1000); 
 DECLARE ch VARCHAR(1); 
 DECLARE i INT;
 SET i = 1;
 SET temp = '';
 IF original REGEXP pattern THEN 
  loop_label: LOOP 
   IF i>CHAR_LENGTH(original) THEN
    LEAVE loop_label;  
   END IF;
   SET ch = SUBSTRING(original,i,1);
   IF NOT ch REGEXP pattern THEN
    SET temp = CONCAT(temp,ch);
   ELSE
    SET temp = CONCAT(temp,replacement);
   END IF;
   SET i=i+1;
  END LOOP;
 ELSE
  SET temp = original;
 END IF;
 RETURN temp;
END$$

DELIMITER ;

Example execution:

mysql> select regex_replace('[^a-zA-Z0-9\-]','','2my test3_text-to. check \\ my- sql (regular) ,expressions ._,');
csdnceshi76
斗士狗 I've modified this method to attempt to address some of the limitations mentioned above and more. Please see this answer.
大约 4 年之前 回复
csdnceshi63
elliott.david someone else did that in this answer – just as a reference in case the link dies ;)
4 年多之前 回复
csdnceshi63
elliott.david Nice – but unfortunately doesn't deal with references like select regex_replace('.*(abc).*','\1','noabcde') (returns 'noabcde', not 'abc').
4 年多之前 回复
csdnceshi72
谁还没个明天 It would be more helpful to actually include code in you answer instead of posting a naked link.
接近 5 年之前 回复
csdnceshi53
Lotus@ I'll just reinforce the above point: this function replaces characters that match a single-character expression. It says above that it is used "to repalce strings using regular expressions", and that can be a little misleading. It does its job, but it's not the job being asked for. (Not a complaint - it is just to save leading people down the wrong path)
8 年多之前 回复
weixin_41568110
七度&光 It also only works on single characters..
接近 9 年之前 回复
立即提问
相关内容推荐