Regular expressions: Matching in column values (wildcard matching whole column) 1 basic character matching
SELECT Prod_name
From Products
WHERE prod_name REGEXP ' 1000 '
Order BY Prod_name;
SELECT Prod_name
From Products
WHERE prod_name REGEXP '. 000 '
Order BY Prod_name;
. is a special character in the regular expression language that represents matching any one character
Use like and wildcard characters to represent:
SELECT Prod_name
From Products
WHERE prod_name like '%000 '
Order BY Prod_name;
Output:
JetPack 1000
JetPack 2000
Examples illustrate the difference between like and regexp:
SELECT Prod_name
From Products
WHERE prod_name like ' 1000 '
Order BY Prod_name;
Do not return data
SELECT Prod_name
From Products
WHERE prod_name REGEXP ' 1000 '
Order BY Prod_name;
Back to JetPack 1000
Like matches this column, if the matched text appears in the column value, like will not be found.
RegExp matches in a column value, and the corresponding row is returned if the matched text appears in the column value. RegExp can also use ^ and $ to match an entire column.
Matching is case-insensitive, and if you want to be case-sensitive, you can use the BINARY keyword (WHERE prod_name REGEXP BINARY ' JetPack. ') 2OR match
| The or Match of a bit regular expression representing one of the matches
SELECT Prod_name
From Products
WHERE prod_name REGEXP ' 1000|2000 '
Order BY Prod_name;
Output:
JetPack 1000
JetPack 3 matches one of several characters
SELECT Prod_name
From Products
WHERE prod_name REGEXP ' [123] Ton '
Order BY Prod_name;
Output:
1 Ton Anvil
2 Ton Anvil
SELECT Prod_name
From Products
WHERE prod_name REGEXP ' 1|2|3 Ton '
Order BY Prod_name;
Match 1 or 2 or 3 ton
[^123] matches any character other than these characters 4 match range
[0-9] [A-z] 5 matching special characters
SELECT Prod_name
From Products
WHERE prod_name REGEXP '. '
Order BY Prod_name;
Matches any character, each row is retrieved
\ to escape
SELECT Prod_name
From Products
WHERE prod_name REGEXP ' \. '
Order BY Prod_name;
The.
White-space Meta characters
\f Page Change
\ n Line Change
\ r Carriage Return
\ t tab
\v Vertical Tabulation
\ reverse Slash
Most regular expression implementations use a single backslash to escape special characters, MySQL requires two backslashes \ (MySQL interprets one itself, the regular expression library interprets one) 6 matching character classes (using predefined character sets) 7 matches multiple instances 0 or more matches 1 or more matches (equal to {1,})
? 0 or one match (equal to {0,1})
{n} specified number of matches
{n,} not less than the specified number of matches
{N,m} match number of ranges (m not exceeding 255) 8 Locator
^ Beginning of the text
$ End of text
[[:;:]] The beginning of the word
[[:;:]] The end of the word