How to use SQL LIKE

The SQL LIKE operator is one of the most important tools in SQL for efficiently searching records for patterns. It uses placeholders and patterns to significantly reduce the records that need to be analyzed. That way you can simplify and speed up your SQL queries.

What is the SQL LIKE operator?

The SQL LIKE operator facilitates data analyses. It allows you to search records and strings for exact matches or to use placeholders and patterns. SQL placeholders are also called wildcards and are only used with the LIKE operator. They ensure that you can filter data efficiently and flexibly and significantly optimize your SQL performance. The LIKE operator is often used in a WHERE clause and the SQL commands DELETE, SELECT and UPDATE.

What is the syntax of SQL LIKE?

The basic syntax of SQL LIKE looks like this:

SELECT  columnA,  columnB, …
FROM tableA
WHERE columnA  LIKE 'pattern or placeholder'

It uses the following parameters:

• SELECT: Specifies the columns that you’ll retrieve data from. You can use an asterisk here to retrieve all columns.
• FROM: Defines which tables are a part of the search.
• WHERE: Defines specific columns for the query.
• LIKE: Specifies the search pattern. You can use character strings for exact matches or use SQL wildcards.

What are the SQL wildcards for LIKE?

SQL wildcards can be used for SQL searches to substitute one or more characters in a string. The most important wildcards are:

• Percent sign %: The % sign replaces zero or more characters in a string. If you enter %search term%, it will return all the instances of that search term within the dataset. It doesn’t matter where in the dataset the search term appears. If you enter s%m you’ll get all results that begin with an “s” and end with an “m”.
• Underscore _: An underscore replaces a single character in an SQL search. If you enter SA_AH, you can filter for names like “SARAH” and “SALAH”. The name “SASHAH” would not be returned, as there is more than one character between “SA” and “AH”.
• Square brackets […]: Searches for any one of the characters that’s between the brackets. So [abc] will search for “a” “b” or “c”.

What can you use SQL LIKE for?

The use cases for queries and searches with SQL LIKE are near limitless. For example, you can:

• Search for names that begin or end with a certain string, if you don’t know an exact name
• Look for websites that end in .org
• Look for exact matches, by leaving out wildcards
• Look for products with a certain word in their name
• Exclude certain data, by using the NOT operator with LIKE
• Filter results based on date, date range or numerical values like prices

Examples of searches with SQL LIKE

Different use cases and datasets will call for different LIKE commands. Below we show you some practical examples that illustrate how SQL LIKE works.

Exact searches

Let’s say you’re searching for customers with a specific name in the table “Customers”. You can use SQL LIKE to find all customers with a specific name. The same is true for exact numerical values like product prices.

Example:

SELECT  *
FROM  customers
WHERE  lastname  LIKE  'Diaz'

Using the percent sign

If you need to search large datasets for patterns, you’ll want to use SQL wildcards. You can use the percent sign to search for all the last names that start with “J” and end with “S”.

SELECT  *
FROM  customer
WHERE  lastname  LIKE  'j%s'

You can also look for all email addresses that end in .edu:

SELECT  *
FROM  customers
WHERE  emailaddress  LIKE  '%.edu'

Using the underscore

If you’re looking for a name but don’t know what the middle letter is, you can use the underscore. The search will look as follows:

SELECT  *
FROM  customers
WHERE  lastname  LIKE  'J_son'

Excluding data with NOT LIKE

You can also reverse a search query with SQL LIKE using SQL NOT. This will filter for results that don’t match the search term. For example, if you want to search for all customers in your list who don’t live in California:

SELECT  *
FROM  customers
WHERE  state  NOT LIKE  'California'

Are there alternatives to SQL LIKE?

SQL LIKE is just one of the comparison operators in SQL. The advantage of LIKE is that you can use SQL wildcards to create placeholders and patterns, which ensures more flexibility and more precise results.

Some other comparison operators include:

• Equal to =: Returns results that match the search term exactly
• Not equal to !=: Returns results that do not match the search term exactly
• Less than < and greater than >: Return numerical values from records that are less than or greater than the value searched for. You can use <= / >= to get results that are less than or equal to / greater than or equal to the value you searched for.