Back to articles list March 4, 2021 - 7 minutes read What Do the Operators LIKE and NOT LIKE Do? Andrew Bone Andrew is a technical writer from Dublin, Ireland. He started his career as a full-stack software developer. However, over time, he found himself enjoying writing documentation and user guides more than programming. He is now focused on breaking down complex subjects so that the writing is clear and understandable, regardless of the readers’ technical background. Tags: sql learn sql sql operators The SQL LIKE and NOT LIKE operators are used to find matches between a string and a given pattern. They are part of standard SQL and work across all database types, making it essential knowledge for all SQL users. Gaining an understanding of these operators will allow you to write better queries and demonstrate to potential employers that you have the skills required to filter through expansive data sets. The SQL LIKE is a logical operator that checks whether or not a string contains a specified pattern. A simple example of this is when you try to find if a name column contains any four-letter name starting with J (such as “John”). The LIKE operator is often used in the WHERE clause of SELECT, DELETE, and UPDATE statements to filter data based on patterns. Becoming proficient in using the LIKE operator will allow you to parse through large databases with ease, and retrieve exactly the data you need. This article will give you a strong foundation in the LIKE operator. If you would like to further advance your skills in this area, consider enrolling in this comprehensive, complete learning track from LearnSQL.com, where you will practice SQL JOINs, aggregations with GROUP BY, and more advanced topics. Let’s examine how we can use the LIKE operator to filter the data returned, thereby retrieving only the desired records. Here is the syntax of the LIKE operator: SELECT column_1, column_2, ... column_n FROM table_name WHERE column_1 LIKE pattern Let’s apply this syntax to a practical example. Imagine we have a table called person_info containing information about people’s first name, last name, and age. FirstNameLastNameAge TimSamuels46 JohnPeterson23 TomWaters42 SarahHenderson48 PaulJohnson34 AndrewWade39 SarahSmith30 LynnPeterson27 Let’s use the LIKE operator to extract the information for people with the last name “Peterson”. SELECT FirstName, LastName, Age FROM person_info WHERE LastName LIKE 'Peterson' Executing this SQL query would yield the following result set: FirstNameLastNameAge JohnPeterson23 LynnPeterson27 This is a simple use case for the LIKE operator. For simple cases like this, we could have also written: SELECT FirstName, LastName, Age FROM person_info WHERE LastName = 'Peterson' So, what is the difference between using LIKE and equals? Equals (=) is a comparison operator that operates on numbers and strings. When comparing strings, the equals operator compares whole strings. In comparison, LIKE compares character by character through the use of wildcards, which will be discussed in detail in this article. The SQL LIKE and the Wildcards In addition to examining the use of SQL LIKE and NOT LIKE, we will look at two wildcard characters: percent (%) and underscore (_). Wildcard characters are used to substitute for one or more characters in a pattern string: The percent (%) wildcard substitutes for one or more characters in a string. The underscore (_) wildcard substitutes for exactly one character in a string. The Underscore (_) Wildcard Let’s look at the underscore (_) wildcard first and apply it to our person_info table. Imagine we want to retrieve, from the table person_info, the first names of the persons with the following conditions: The FirstName must start with the letter “T”, The third letter of FirstName must be “m”, and The second letter FirstName can be anything. We can use the following query: SELECT FirstName FROM person_info WHERE FirstName LIKE 'T_m' The result of this query is: SELECT FirstName FROM person_info WHERE FirstName LIKE 'T_m' The result of this query is: FirstName Tim Tom Notice how the second letter of the name can be anything. Our SQL query is ignoring that letter and looking for the pattern we have specified. Let’s look at another example. This time, we will substitute two characters with two underscore (_) wildcard characters. SELECT FirstName, LastName FROM person_info WHERE LastName LIKE 'Wa__' Executing this query retrieves a single record from our table. FirstNameLastName AndrewWade Notice how Andrew Wade matches the pattern but not Tom Waters. By placing two underscores after “Wa”, we explicitly specify that the LastName we are looking for is 4 characters long. The underscore wildcard can be placed anywhere in the pattern you are looking for. “_om”, “T_m”, or “To_”, are all valid patterns. The Percent (%) Wildcard Let’s now look at the percent (%) wildcard in detail and apply it to our person_info table. The percent (%) wildcard is used to substitute for multiple characters. As an example, imagine we want to find all the people whose last name ends in “son”. To achieve this, we can simply write the following query: SELECT FirstName, LastName FROM person_info WHERE LastName LIKE '%son' The result of this query is: FirstNameLastName JohnPeterson SarahHenderson PaulJohnson LynnPeterson Notice how the number of characters before “son” does not matter with this wildcard. If the pattern you are looking for is embedded in the middle of another string, you can use the percent wildcard at the beginning and at the end of the pattern. For example, to find all employees whose last names contain “er”, use the following query with the pattern '%er%'. SELECT FirstName, LastName FROM person_info WHERE LastName LIKE '%er%' Executing this query yields the following result set: FirstNameLastName JohnPeterson TomWaters SarahHenderson LynnPeterson Let’s combine the use of both _ and %. Say we want to find all the first names whose second character is the letter “a”. We can do this with the following query: SELECT FirstName, LastName FROM person_info WHERE FirstName LIKE '_a%' Executing this query results in this set: FirstNameLastName SarahHenderson PaulJohnson PaulaSmith The LIKE operator is most commonly used in conjunction with the WHERE clause. However, it can be used anywhere you use an expression in SQL. SQL NOT LIKE SQL NOT LIKE behaves as you might expect, essentially returning the opposite of what the LIKE operator would. Let’s substitute LIKE with NOT LIKE in one of our earlier examples and see the effect. SELECT FirstName, LastName, Age FROM person_info WHERE LastName NOT LIKE 'Peterson' Here is the result set: FirstNameLastNameAge TimSamuels46 TomWaters42 SarahHenderson48 PaulJohnson34 AndrewWade39 PaulaSmith30 As you can see, using NOT LIKE negates the pattern you specify. This is similar to other NOT operators you see in SQL. In this case, the NOT LIKE operator retrieves data for all persons whose last name is not Peterson. SQL LIKE Case Sensitivity For the above examples, the name “Peterson” was used with a capital letter. Depending on the variant of SQL you are using, the LIKE operator may be case-sensitive. MySQL, PostgreSQL, and Oracle are all case-sensitive by default. SQL Server can be a little trickier. By default, SQL Server is not case-sensitive. However, it is possible to create a case-sensitive SQL Server database and even make specific table columns case-sensitive. LIKE and NOT LIKE Other Than in a WHERE Clause It is common to see LIKE and NOT LIKE used in combination with a WHERE clause. However, the LIKE operator can be used in any valid SQL statement, such as SELECT, INSERT INTO, UPDATE, or DELETE. Let’s see this with an example executed against our person_info table. Consider the following SQL query: SELECT FirstName, FirstName LIKE 'T%' AS starts_with_t FROM person_info; The SELECT statement here does not just retrieve the data from the FirstName column. Paired with LIKE, the second column selected in this query returns a Boolean value based on FirstName for each record in the table. The pattern we’ve specified is any string starting with the letter “T”, so this query checks every FirstName to see if it starts with the letter “T” and returns true (1) if it does, false (0) otherwise. Executing this query generates the following result set: FirstNamestarts_with_t Tim1 John0 Tom1 Sarah0 Paul0 Andrew0 Paula0 Lynn0 SQL LIKE and NOT LIKE Are Essential for Filtering Data We have covered most situations for using the LIKE operator. Understanding the SQL LIKE operator is essential, and if you apply for any jobs in which the use of SQL is required, it may be one of the SQL interview questions they ask. Mastery of the LIKE operator and these two wildcard characters comes with practice, so this interactive SQL course from LearnSQL.com with 88 distinct exercises is a great resource for those looking to improve knowledge in this area. Tags: sql learn sql sql operators You may also like Using AND, OR, and NOT Operators in SQL Want to learn more about AND, OR, and NOT operators in SQL? This article teaches you to use these operators and bring your WHERE clauses to the next level! Read more What Is the SQL WHERE Clause? Are you confused about the SQL WHERE clause? Learn what it does and how it can improve your SQL queries. Read more HAVING vs. WHERE in SQL: What You Should Know To take advantage of SQL’s great power, you must understand HAVING vs. WHERE clauses. How do you use them? What are their differences? Read more What SQL Practice Do You Need to Prepare for a Technical Job Interview? Need some SQL practice before a technical job interview? Here are six ways to prepare for various types of database and SQL jobs! Read more Learning SQL? 12 Ways to Practice SQL Online Want to learn SQL or practice SQL online? We’ve rounded up the top ways to practice SQL so you can go from learning to earning! Read more Why Window Functions Are Not Allowed in WHERE Clauses Window functions can only appear in SELECT and ORDER BY but not in WHERE clauses. The reason is the logical order in which SQL queries are processed. Read more Subscribe to our newsletter Join our weekly newsletter to be notified about the latest posts.