LIKE

Date: April 30th 2016
Last updated: April 30th 2016

The LIKE operator is used in a WHERE clause to limit the result set and return only records that match the given pattern. Also see REGEXP.

Useful resources

• http://www.w3schools.com/sql/sql_like.asp
• http://www.w3schools.com/sql/sql_wildcards.asp
• http://www.w3resource.com/mysql/string-functions/mysql-like-function.php
• http://www.tutorialspoint.com/sql/sql-like-clause.htm

All countries starting with 'a'

SELECT distinct(country) FROM
my_world_table WHERE 
country LIKE 'a%';

All countries ending with 'a'

SELECT distinct(country) FROM
my_world_table WHERE 
country LIKE '%a';

All countries containing 'and'

SELECT distinct(country) FROM
my_world_table WHERE 
country LIKE '%and%';

All countries ending with any letter followed by 'and'

SELECT distinct(country) FROM
my_world_table WHERE 
country LIKE '%_and';

All countries starting with 'l','m','n', or 'o' and ends with any letter followed by 'and' (e.g. New Zealand)

SELECT distinct(country) FROM
my_world_table WHERE 
country LIKE '[lmno]%_and';

abbreviate the above syntax using [l-o]

SELECT distinct(country) FROM
my_world_table WHERE 
country LIKE '[l-o]%_and';

Find everything without the pattern above

SELECT distinct(country) FROM
my_world_table WHERE 
country NOT LIKE '[l-o]%_and';

/* OR */

SELECT distinct(country) FROM
my_world_table WHERE 
country LIKE '[!l-o]%_and';