In MySQL, the REGEXP operator is used to determine whether or not a string matches a regular expression. It’s a synonym for
.
If the string matches the regular expression provided, the result is
1, otherwise it’s
0.
Syntax
The syntax goes like this:
expr REGEXP pat
Where
expr is the input string and
pat is the regular expression for which you’re testing the string against.
Example 1 – Basic Regular Expression
The most basic regular expression we can use is one that has no
special characters in it. Here, we just use a string. If any part of the
input string matches that string, it returns a match.
SELECT
'Corn' REGEXP 'Corn' AS 'Corn',
'Acorn' REGEXP 'Corn' AS 'Acorn',
'Corner' REGEXP 'Corn' AS 'Corner',
'Cheese' REGEXP 'Corn' AS 'Cheese';
Result:
+------+-------+--------+--------+
| Corn | Acorn | Corner | Cheese |
+------+-------+--------+--------+
| 1 | 1 | 1 | 0 |
+------+-------+--------+--------+
Example 2 – Match the Beginning of a String
In this example, the regular expression specifies that the string must begin with
Co.
SELECT
'Corn' REGEXP '^Co' AS 'Corn',
'Acorn' REGEXP '^Co' AS 'Acorn',
'Cheese' REGEXP '^Co' AS 'Cheese';
Result:
+------+-------+--------+
| Corn | Acorn | Cheese |
+------+-------+--------+
| 1 | 0 | 0 |
+------+-------+--------+
Example 3 – Match the End of a String
In this example, the regular expression specifies that the string must end with
rn.
SELECT
'Corn' REGEXP 'rn$' AS 'Corn',
'Acorn' REGEXP 'rn$' AS 'Acorn',
'Cheese' REGEXP 'rn$' AS 'Cheese';
Result:
+------+-------+--------+
| Corn | Acorn | Cheese |
+------+-------+--------+
| 1 | 1 | 0 |
+------+-------+--------+
Example 4 – Match Any Character
The
. character matches any character.
SELECT
'Corn' REGEXP '.' AS 'Corn',
'Cheese' REGEXP '.' AS 'Cheese',
'' REGEXP '.' AS '';
Result:
+------+--------+---+
| Corn | Cheese | |
+------+--------+---+
| 1 | 1 | 0 |
+------+--------+---+
This character is typically used in conjunction with other characters to specify further criteria. For example:
SELECT
'Corn' REGEXP '^C.rn$' AS 'Corn',
'Crn' REGEXP '^C.rn$' AS 'Crn';
Result:
+------+-----+
| Corn | Crn |
+------+-----+
| 1 | 0 |
+------+-----+
Here we specify that the string must start with
C, that it must be followed by a character (any character), and that it must end with
rn.
Note that this character specifies a single instance of the character. If you want to specify multiple instances (for example
ee instead of just
e), you’ll need to add more
. characters.
SELECT
'Tweet' REGEXP '^Tw..t$' AS 'Tweet',
'Tweat' REGEXP '^Tw..t$' AS 'Tweat',
'Tweet' REGEXP '^Tw.t$' AS 'Tweet',
'Twit' REGEXP '^Tw..t$' AS 'Twit';
Result:
+-------+-------+-------+------+
| Tweet | Tweat | Tweet | Twit |
+-------+-------+-------+------+
| 1 | 1 | 0 | 0 |
+-------+-------+-------+------+
Another way to do this is to specify the number of occurrences within curly brackets:
SELECT
'Tweet' REGEXP '^Tw.{2}t$' AS 'Tweet',
'Tweat' REGEXP '^Tw.{2}t$' AS 'Tweat',
'Tweet' REGEXP '^Tw.{1}t$' AS 'Tweet',
'Twit' REGEXP '^Tw.{2}t$' AS 'Twit';
Result:
+-------+-------+-------+------+
| Tweet | Tweat | Tweet | Twit |
+-------+-------+-------+------+
| 1 | 1 | 0 | 0 |
+-------+-------+-------+------+
However, if you know the character that you’re looking for, you can specify that character (instead of the
. character), as demonstrated in the following example.
Example 5 – Match Zero or More Instances of a Specific Character
We can do the following to specify zero or more instances of the
e character:
SELECT
'Twet' REGEXP '^Twe*t$' AS 'Twet',
'Tweet' REGEXP '^Twe*t$' AS 'Tweet',
'Tweeet' REGEXP '^Twe*t$' AS 'Tweeet',
'Twt' REGEXP '^Twe*t$' AS 'Twt',
'Twit' REGEXP '^Twe*t$' AS 'Twit',
'Twiet' REGEXP '^Twe*t$' AS 'Twiet',
'Tweit' REGEXP '^Twe*t$' AS 'Tweit';
Result:
+------+-------+--------+-----+------+-------+-------+
| Twet | Tweet | Tweeet | Twt | Twit | Twiet | Tweit |
+------+-------+--------+-----+------+-------+-------+
| 1 | 1 | 1 | 1 | 0 | 0 | 0 |
+------+-------+--------+-----+------+-------+-------+
The first four match but the last three don’t.
Example 6 – Match One or More Instances of a Specific Character
We can modify the previous example so that we only get a match if
one or more characters is found (the previous example returned a match if
zero or more were found). To do this, we simply use
+ instead of
*:
SELECT
'Twet' REGEXP '^Twe+t$' AS 'Twet',
'Tweet' REGEXP '^Twe+t$' AS 'Tweet',
'Tweeet' REGEXP '^Twe+t$' AS 'Tweeet',
'Twt' REGEXP '^Twe+t$' AS 'Twt',
'Twit' REGEXP '^Twe+t$' AS 'Twit',
'Twiet' REGEXP '^Twe+t$' AS 'Twiet',
'Tweit' REGEXP '^Twe+t$' AS 'Tweit';
Result:
+------+-------+--------+-----+------+-------+-------+
| Twet | Tweet | Tweeet | Twt | Twit | Twiet | Tweit |
+------+-------+--------+-----+------+-------+-------+
| 1 | 1 | 1 | 0 | 0 | 0 | 0 |
+------+-------+--------+-----+------+-------+-------+
In this case, the fourth word returns a different result to the previous example.
Example 7 – Match Zero or One Instance of a Specific Character
We can modify the previous example so that we only get a match on zero or one of the desired characters. To do this, we use
?:
SELECT
'Twet' REGEXP '^Twe?t$' AS 'Twet',
'Tweet' REGEXP '^Twe?t$' AS 'Tweet',
'Tweeet' REGEXP '^Twe?t$' AS 'Tweeet',
'Twt' REGEXP '^Twe?t$' AS 'Twt',
'Twit' REGEXP '^Twe?t$' AS 'Twit',
'Twiet' REGEXP '^Twe?t$' AS 'Twiet',
'Tweit' REGEXP '^Twe?t$' AS 'Tweit';
Result:
+------+-------+--------+-----+------+-------+-------+
| Twet | Tweet | Tweeet | Twt | Twit | Twiet | Tweit |
+------+-------+--------+-----+------+-------+-------+
| 1 | 0 | 0 | 1 | 0 | 0 | 0 |
+------+-------+--------+-----+------+-------+-------+
Example 8 – Alternation
You can use the
| character to match one or another sequence of characters:
SELECT
'Tweet' REGEXP 'Tw|et' AS 'Tweet',
'For Let' REGEXP 'Tw|et' AS 'For Let',
'Banana' REGEXP 'Tw|et' AS 'Banana';
Result:
+-------+---------+--------+
| Tweet | For Let | Banana |
+-------+---------+--------+
| 1 | 1 | 0 |
+-------+---------+--------+
Here’s another example where I search for whole words:
SELECT
'Cat' REGEXP 'Cat|Dog' AS 'Cat',
'Dog' REGEXP 'Cat|Dog' AS 'Dog',
'Doggone' REGEXP 'Cat|Dog' AS 'Doggone',
'Banana' REGEXP 'Cat|Dog' AS 'Banana';
Result:
+-----+-----+---------+--------+
| Cat | Dog | Doggone | Banana |
+-----+-----+---------+--------+
| 1 | 1 | 1 | 0 |
+-----+-----+---------+--------+
We still get a match even when our regular expression matches only part of the the string.
Example 9 – Match Zero or More Instances of a Sequence
You can use brackets along with the asterisk
()* to specify zero or more instances of a sequence:
SELECT
'Banana' REGEXP '(an)*' AS 'Banana',
'Land' REGEXP '(an)*' AS 'Land',
'Cheese' REGEXP '(an)*' AS 'Cheese';
Result:
+--------+------+--------+
| Banana | Land | Cheese |
+--------+------+--------+
| 1 | 1 | 1 |
+--------+------+--------+
Another example:
SELECT
'Banana' REGEXP '^B(an)*d$' AS 'Banana',
'Band' REGEXP '^B(an)*d$' AS 'Band',
'Bald' REGEXP '^B(an)*d$' AS 'Bald',
'Bad' REGEXP '^B(an)*d$' AS 'Bad';
Result:
+--------+------+------+-----+
| Banana | Band | Bald | Bad |
+--------+------+------+-----+
| 0 | 1 | 0 | 0 |
+--------+------+------+-----+
Example 10 – Repetition
As seen in a previous example, you can use curly brackets to specify
repetition. This notation provides a more general way of writing regular
expressions than some of the previous examples:
SELECT
'Tweeet' REGEXP 'e{3}' AS 'Tweeet',
'Tweet' REGEXP 'e{3}' AS 'Tweet';
Result:
+--------+-------+
| Tweeet | Tweet |
+--------+-------+
| 1 | 0 |
+--------+-------+
Example 11 – Range
You can use the hyphen character to specify a range. Here’s an example that specifies a range of numbers:
SELECT
'Tweet 123' REGEXP '[0-9]' AS 'Tweet 123',
'Tweet ABC' REGEXP '[0-9]' AS 'Tweet ABC';
Result:
+--------+-------+
| Tweeet | Tweet |
+--------+-------+
| 1 | 0 |
+--------+-------+
And the following example specifies a range of letters:
SELECT
'Tweet 123' REGEXP '[A-Z]' AS 'Tweet 123',
'ABC' REGEXP '[A-Z]' AS 'ABC',
'123' REGEXP '[A-Z]' AS '123';
Result:
+--------+-------+
| Tweeet | Tweet |
+--------+-------+
| 1 | 0 |
+--------+-------+
Here’s what happens if we limit the range of numbers:
SELECT
'123' REGEXP '[1-3]' AS '123',
'012' REGEXP '[1-3]' AS '012',
'045' REGEXP '[1-3]' AS '045';
Result:
+-----+-----+-----+
| 123 | 012 | 045 |
+-----+-----+-----+
| 1 | 1 | 0 |
+-----+-----+-----+
Example 12 – Not Within a Range
We can use the
^ character to modify the previous example so that the specified range of characters are excluded:
SELECT
'123' REGEXP '[^1-3]' AS '123',
'012' REGEXP '[^1-3]' AS '012',
'045' REGEXP '[^1-3]' AS '045';
Result:
+-----+-----+-----+
| 123 | 012 | 045 |
+-----+-----+-----+
| 0 | 1 | 1 |
+-----+-----+-----+
So we get the opposite result to the previous example.
Posts
![IVRAMANA](https://draft.blogger.com/img/profile_blank_gray.jpg" title="IVRAMANA)
