REGEXP

Date: April 26th 2016
Last updated: April 26th 2016

The REGEXP operator reduces the repetition of SQL syntax. Such as chaining a series of LIKE clauses. For example change WHERE col LIKE '%a' OR col LIKE '%b' (item ending with 'a' or 'b') to WHERE col REGEXP '(a|b)$'. It is also easy to get everything that does not end with a or b by adding NOT to the clause. E.g. WHERE col NOT REGEXP '(a|b)$'

• http://www.w3resource.com/mysql/string-functions/mysql-regexp-function.php

country names starting with vowels (a, e, i, o, u)

SELECT distinct(country) FROM
my_world_table WHERE 
country REGEXP 
'^a(.*)|^e(.*)|^i(.*)|^o(.*)|^u(.*)';

country names ending with vowels (a, e, i, o, u)

SELECT distinct(country) FROM
my_world_table WHERE 
country REGEXP 
'(.*)a$|(.*)e$|(.*)i$|(.*)o$|(.*)u$'; ;

country names with vowels as both their first and last characters

SELECT distinct(country) FROM
my_world_table WHERE 
country REGEXP 
'^(a|e|i|o|u)(.*)(a|e|i|o|u)$'

country names NOT having a vowel as the first letter

SELECT distinct(country) FROM
my_world_table WHERE 
country NOT REGEXP 
'^(a|i|o|e|u)(.*)'

country names NOT having a vowel as the first letter

SELECT distinct(country) FROM
my_world_table WHERE 
country NOT REGEXP 
'^(a|i|o|e|u)(.*)'

country names that don't end with a vowel

SELECT distinct(country) FROM
my_world_table WHERE 
country NOT REGEXP 
'(.*)(a|e|i|o|u)$'

country names that don't start with OR end with a vowel

SELECT distinct(country) FROM
my_world_table WHERE 
country NOT REGEXP 
'^(a|e|i|o|u)' OR
country NOT REGEXP 
'(a|e|i|o|u)$';

country names that don't start with AND end with a vowel

SELECT distinct(country) FROM
my_world_table WHERE 
country NOT REGEXP 
'^(a|e|i|o|u)' AND 
country NOT REGEXP 
'(a|e|i|o|u)$'