Back to list Standard SQL How to Use LIKE in SQL Database: SQL PostgreSQL Oracle SQL Server MySQL SQLite Operators:LIKE, WHERE Problem: You’d like to find a substring matching a pattern in a string. Example: Our database has a table named customer with data in the following columns: id, first_name, last_name, and city. idfirst_namelast_namecity 1AlanWatsonMadrid 2LisaBlackSingapore 3LauraWilliamsSeattle 4MilanBrickSeoul Let’s see some examples of pattern matching in SQL. Example 1: Let’s display the first name, last name, and city of each customer whose city name starts with the letter 'S.' Solution: SELECT last_name, first_name, city FROM customer WHERE city LIKE 'S%'; last_namefirst_namecity LisaBlackSingapore MilanBrickSeoul LauraWilliamsSeattle We used the % wildcard, which represents zero or more unknown characters. This wildcard can be used anywhere in the string. Example 2: Let’s show the first name, last name, and city of each customer whose city name contains exactly five characters. Solution: SELECT last_name, first_name, city FROM customer WHERE city LIKE '_____'; Here’s the result: last_namefirst_namecity MilanBrickSeoul The underscore wildcard can be used to represent a single unknown character. Thus, by using five of them in a row, we can represent all strings that are five characters long. In our example, the only city matching that description is Seoul. Example 3: Let’s show the first name, last name, and city of each customer whose city starts with the letter 'S' and has the letter 'o' as the third-to-last character. Solution: SELECT last_name, first_name, city FROM customer WHERE city LIKE 'S%o__'; Here’s the result: last_namefirst_namecity LisaBlackSingapore MilanBrickSeoul The query returned only two records: Lisa Black from Singapore and Milan Brick from Seoul. These cities match the given pattern. Here, we combined both wildcards to generate a more specific pattern: all strings beginning with a capital letter 'S' and containing any number of other characters after that, so long as the third-to-last character is a lowercase letter 'o.' Discussion: If you want to select records in which a string matches a specific pattern, you can use a LIKE clause as the condition in a WHERE clause. After WHERE, list the name of the column (e.g., city) followed by a LIKE clause specifying the string pattern (e.g., 'S%o__') to search for. As we saw in the examples, you can use two special characters known as wildcards to represent unknown characters: The '%' wildcard denotes zero or more characters. The '_' wildcard denotes any single character. You can combine these wildcards to achieve some really powerful results. You can also chain more than one LIKE condition. Finally, you can use the NOT operator to find strings that don’t match the given pattern. Recommended courses: SQL Basics SQL Basics in SQL Server SQL Practice Set Recommended articles: How to Use LIKE in SQL: SQL Pattern Matching How to Use a SQL Wildcard See also: How to Trim Strings in SQL How to Replace Part of a String in SQL Tags: SQL PostgreSQL Oracle SQL Server MySQL SQLite Subscribe to our newsletter Join our weekly newsletter to be notified about the latest posts.