regular expression
regular expression

A regular expression is a powerful way of specifying a pattern for a complex search.

Operators:

NOT REGEXP Negation of REGEXP
REGEXP Pattern matching using regular expressions
RLIKE Synonym of REGEXP

 

Syntax:

A regular expression describes a set of strings. The simplest regular expression is one that has no special characters in it. For example, the regular expression hello matches hello and nothing else.

Nontrivial regular expressions use certain special constructs so that they can match more than one string. For example, the regular expression hello|wordmatches either the string hello or the string word.

  • Expr NOT REGEXP pattern
  • Expr NOT RLIKE pattern
  • Expr REGEXP pattern
  • Expr RLIKE pattern

Query syntax:

  • SELECT column_list FROM table_name WHERE string_column REGEXP pattern;

Example:

Question: Fetch records whose name start with A OR B OR C.

Answer: SELECT DISTINCT NAME FROM STATION WHERE NAME REGEXP ‘^(A|B|C)’;

The pattern allows you to find the product whose name begins with A, B, or C.

  • The character ^ means to match from the beginning of the string.
  • The character | means to search for alternatives if one fails to match.

If you want the REGEXP operator to compare strings in case-sensitive fashion, you can use the BINARY operator to cast a string to a binary string.

Example:

  • SELECT DISTINCT NAME FROM STATION WHERE NAME REGEXP BINARY ‘^(A|B|C)’;

Expressions:

Metacharacter Behavior
^ matches the position at the beginning of the searched string
$ matches the position at the end of the searched string
. matches any single character
[…] matches any character specified inside the square brackets
[^…] matches any character not specified inside the square brackets
p1|p2 matches any of the patterns p1 or p2
* matches the preceding character zero or more times
+ matches preceding character one or more times
{n} matches n number of instances of the preceding character
{m,n} matches from m to n number of instances of the preceding character

 

LEAVE A REPLY

Please enter your comment!
Please enter your name here