RLIKE Operator in MySQL

The RLIKE operator is a synonym of the regexp operator. It works the same way as the regexp operator. It uses the same metacharacters as well. Let us look at some examples to understand

Filter records for zero or more occurrences

select * from film where title rlike 'TE*';

Filter records for one or more occurrences

select * from film where title rlike 'TE+';

Return records that have digits

select * from film where rating rlike '[0-9]';

Filter records that have no digits

select * from film where rating rlike '^([^0-9]*)$';

Not RLIKE Operator

Similarly, we can have examples with the Not RLIKE operator.

Get all records with ratings that have digits in them

select * from film where rating NOT rlike '^([^0-9]*)$';

Get all records that do not follow a certain pattern. Let's say we want all the names of actors that do not have the following pattern

<some characters between H and M> + <exactly one character> + T

We can use the following,

SELECT FIRST_NAME FROM ACTOR WHERE FIRST_NAME NOT RLIKE '[H-M].[T]';