How the RLIKE Operator Works in MySQL

In MySQL, the RLIKE operator is used to determine whether or not a string matches a regular expression. It’s a synonym for REGEXP_LIKE().

If the string matches the regular expression provided, the result is 1, otherwise it’s 0.

Syntax

The syntax goes like this:

expr RLIKE pat

Where expr is the input string and pat is the regular expression for which you’re testing the string against.

Example

Here’s an example of how to use this operator in a SELECT statement:

SELECT 'Tweet' REGEXP '^Tw.*t$';

Result:

+--------------------------+
| 'Tweet' REGEXP '^Tw.*t$' |
+--------------------------+
|                        1 |
+--------------------------+

In this case, the return value is 1 which means that the input string matched the regular expression. In particular, we specified that the input string should start with Tw and end with t (this is because we started the pattern with ^Tw and ended it with t$). The . part specifies any character, and * specifies that it could be zero to any number of that (any) character. So .* means that there can be no characters, one character, or many characters in between the start and end.
Here’s what happens if we drop the *:

SELECT 'Tweet' REGEXP '^Tw.t$';

Result:

+-------------------------+
| 'Tweet' REGEXP '^Tw.t$' |
+-------------------------+
|                       0 |
+-------------------------+

The return result is 0 which means no match. This is because . specifies only one instance of any character. Our input string contains two instances.
Here are some permutations:

SELECT 
  'Twet' REGEXP '^Tw.t$' AS 'Twet',
  'Twit' REGEXP '^Tw.t$' AS 'Twit',
  'Twt' REGEXP '^Tw.t$' AS 'Twt',
  'Tw.t' REGEXP '^Tw.t$' AS 'Tw.t';

Result:

+------+------+-----+------+
| Twet | Twit | Twt | Tw.t |
+------+------+-----+------+
|    1 |    1 |   0 |    1 |
+------+------+-----+------+

More Examples

For more examples, see MySQL REGEXP Examples. Like RLIKE, the REGEXP operator is also a synonym for REGEXP_LIKE().

3 Ways to Detect if a String Matches a Regular Expression in MySQL

MySQL has a number of functions and operators that allow us to perform operations using regular expressions (regex). This article presents two operators and one function that enable us to find out if a string matches a regular expression specified by a given pattern.

These regex functions and operators are:

• REGEXP_LIKE()
• REGEXP
• RLIKE

These are all basically equivalent, as the operators (the second two) are both synonyms of the function (the first one). In any case, you can see examples of all three in action below.

The REGEXP_LIKE() Function

First, let’s look at the function. Here’s an example of running a regex search using the REGEXP_LIKE() function:

SELECT 
  REGEXP_LIKE('Car', '^C') AS 'Match',
  REGEXP_LIKE('Bar', '^C') AS 'No Match';

Result:

+-------+----------+
| Match | No Match |
+-------+----------+
|     1 |        0 |
+-------+----------+

The first string matches (because it starts with C) so the result is 1. The second string doesn’t match and so the result is 0.

However, this function can be much more useful than simply returning a 1 or 0. For example, it can be added to the WHERE clause when querying a database. In this case, we can get a list of rows that contain a match for the pattern.

Here’s an example:

SELECT AlbumId, AlbumName
FROM Albums
WHERE REGEXP_LIKE(AlbumName, '^Power');

Result:

+---------+------------+
| AlbumId | AlbumName  |
+---------+------------+
|       1 | Powerslave |
|       2 | Powerage   |
+---------+------------+

Here’s the full table:

SELECT AlbumId, AlbumName
FROM Albums;

Result:

+---------+--------------------------+
| AlbumId | AlbumName                |
+---------+--------------------------+
|       1 | Powerslave               |
|       2 | Powerage                 |
|       3 | Singing Down the Lane    |
|       4 | Ziltoid the Omniscient   |
|       5 | Casualties of Cool       |
|       6 | Epicloud                 |
|       7 | Somewhere in Time        |
|       8 | Piece of Mind            |
|       9 | Killers                  |
|      10 | No Prayer for the Dying  |
|      11 | No Sound Without Silence |
|      12 | Big Swing Face           |
|      13 | Blue Night               |
|      14 | Eternity                 |
|      15 | Scandinavia              |
|      16 | Long Lost Suitcase       |
|      17 | Praise and Blame         |
|      18 | Along Came Jones         |
|      19 | All Night Wrong          |
|      20 | The Sixteen Men of Tain  |
+---------+--------------------------+

The REGEXP Operator

Here’s how to do the same thing using the REGEXP operator:

SELECT 
  'Car' REGEXP '^C' AS 'Match',
  'Bar' REGEXP '^C' AS 'No Match';

Result:

+-------+----------+
| Match | No Match |
+-------+----------+
|     1 |        0 |
+-------+----------+

And the database example:

SELECT AlbumId, AlbumName
FROM Albums
WHERE AlbumName REGEXP '^Power';

Result:

+---------+------------+
| AlbumId | AlbumName  |
+---------+------------+
|       1 | Powerslave |
|       2 | Powerage   |
+---------+------------+

You can also use NOT REGEXP to return the opposite result.

The RLIKE Operator

And here it is using RLIKE:

SELECT 
  'Car' RLIKE '^C' AS 'Match',
  'Bar' RLIKE '^C' AS 'No Match';

Result:

+-------+----------+
| Match | No Match |
+-------+----------+
|     1 |        0 |
+-------+----------+

And the database example:

SELECT AlbumId, AlbumName
FROM Albums
WHERE AlbumName RLIKE '^Power';

Result:

+---------+------------+
| AlbumId | AlbumName  |
+---------+------------+
|       1 | Powerslave |
|       2 | Powerage   |
+---------+------------+

In this case I simply swapped REGEXP for RLIKE and left the rest of the code alone.

You can also use NOT RLIKE to return the opposite result.

More REGEX Functions

MySQL also includes a few other regex functions and operators.  Three of these are listed below. Technically, you could also use the first two to “detect” whether a string matches a regex pattern (in which case, maybe this article should be titled “5 Ways to Detect if a String Matches a Regular Expression in MySQL” instead of just “3 ways…”).

Anyway, here are three more regex functions:

• You can use the REGEXP_INSTR() function to return the starting index of a substring that matches the regular expression pattern.
• The REGEXP_SUBSTR() function returns the substring that matches the given regular expression pattern.
• And the REGEXP_REPLACE() function replaces occurrences of the substring within a string that matches the given regular expression pattern.

Mysql - IN () Function does not return results using a percent sign

It's short and simple.

I'm using this query:

SELECT * FROM TABLE_NAME WHERE COL_NAME IN('%VALUE1%', '%VALUE2%')

However it doesn't seem to work, I've used LIKE for both values combined using ORand it returns results, so I'm certain that there's rows in the table with these values.. am I missing something here?

Does MYSQL IN() function accept percentage sign? What is the right way to do it?

Thanks in advance!

---

You could try with RLIKE:

SELECT * FROM TABLE_NAME WHERE COL_NAME RLIKE 'VALUE[12]'

Keep in mind these sorts of queries tend to perform very badly because they require a full table scan. If you're doing this a lot you may want to adjust your schema to better represent your usage patterns.