MySQL 5.x only had one operator that allows you to work with regular expressions. This is the REGEXP operator, which works just like the LIKE operator, except that instead of using the _ and % wildcards, it uses a POSIX Extended Regular Expression (ERE). Despite the “extended” in the name of the standard, the POSIX ERE flavor is a fairly basic regex flavor by modern standards. Still, it makes the REGEXP operator far more powerful and flexible than the simple LIKE operator.
MySQL 8.x and 9.x still support the REGEXP operator. But MySQL 8.0 switched the regex engine to the International Components for Unicode (ICU). The ICU regex flavor is a modern, feature-rich regular expression flavor, with particularly strong support for Unicode. MySQL comes bundled with ICU, but on platforms other than Windows it can also use the ICU library provided by the operating system. RegexBuddy supports ICU 55 and later. ICU 55 was released in 2014 and MySQL 8.0 in 2016. That means RegexBuddy should support all combinations of MySQL and ICU that you may come across. The SELECT ICU_VERSION() MySQL statement tells you the version of ICU it is using.
MySQL 8.0 also added 4 new regular expression functions: REGEXP_LIKE, REGEXP_SUBSTR, REGEXP_REPLACE, and REGEXP_INSTR. These significantly expand the usefulness of regular expressions with MySQL database queries. REGEXP_REPLACE() uses the same syntax for replacement strings as ICU, which RegexBuddy also fully supports.
First, use RegexBuddy to define a regex or retrieve a regexp saved in a RegexBuddy library. Rely on RegexBuddy’s clear regex analysis, which is constantly updated as you build the pattern, rather than dealing with the cryptic regex syntax on your own. Detailed help on that syntax is always only a click away.
If you're migrating from MySQL 5.x to 8.x or 9.x, or if you're upgrading from one version of ICU to another, then you need to make sure your regular expressions will still work the way you intended with the new version of ICU. With RegexBuddy, you can easily compare your regular expressions between POSIX ERE and any version or versions of ICU you're working with. You can even convert regular expressions from POSIX ERE to ICU, between any two versions of ICU, or even between any two regex flavors that RegexBuddy supports. If you were using regexes on the client side with MySQL 5.x that you could now add to your database statements with MySQL 8 or 9’s new REGEXP functions then you can compare and convert your regexes between your client side regex flavor and ICU just as easily.
If you created a new regular expression, test and debug it in RegexBuddy before using it in your MySQL queries. Test each regex in RegexBuddy’s safe sandbox without risking precious data. Quickly apply the regex to a wide variety of input and sample data, without having to produce that input through your database.
Finally, let RegexBuddy generate a source code snippet that you can copy and paste directly into whichever database application you use. Just choose what you want to use the regex for, and a fully functional database query is ready. You can change the names of tables and columns to suit your naming style or the current situation, which RegexBuddy automatically remembers.
Don’t bother trying to remember MySQL’s specific regexp syntax or the many arguments that MySQL’s REGEXP functions accept. And don’t worry about properly escaping backslashes and other characters. Just tell RegexBuddy what you want to do, and you will get the proper MySQL code straight away.