Some have additional variations of the LIKE function that are worth mentioning. The LIKE function is largely similar across different flavors of SQL (e.g. On the other hand, how many employees have the initials ‘A.Z.’? SELECTĬOUNT(CASE WHEN first_name LIKE 'A%' AND last_name LIKE 'Z%' THEN 1 END) num_employeesįROM employees Intermediate Examples of SQL LIKE For example, can we find out how many employees with the name ‘Adam’ are in our database? SELECTĬOUNT(CASE WHEN first_name LIKE 'Adam' THEN 1 END) num_employees_adam We also use LIKE in the SELECT clause too. Thus far, we have focused on using LIKE as a condition for selecting records in the WHERE clause. Knowing that we can find out the names of employees whose initials are ‘Z. The LIKE syntax can be applied to multiple columns, as long as their variable type is a variable-length character ( varchar). On the other hand, to find a string that matches multiple LIKE conditions, use the AND keyword. You can combine multiple conditions using the LIKE syntax too.įor example, use the OR condition to find results that satisfy at least one out of multiple LIKE patterns. SQL LIKE with Multiple Values Using OR/AND WHERE lower(first_name) LIKE 'joann_' SELECT DISTINCT `UPPER(column_name) LIKE PATTERN`įor example, to find out if an employee’s name is Joanne, JoAnne, Joanna, or JoAnna, try either of the following. Be sure to spell out the pattern in CAPITAL LETTERS. You could replace LOWER with UPPER in the syntax above too. When using the syntax, make sure you spell the pattern in all lowercase! Else, you might not get any matches. The function LOWER() returns all strings in lowercase, regardless of whether they are stored as upper-, lower- or mixed case. If you need to perform pattern matching but aren’t sure if the string is stored in lowercase, uppercase, or mixed case, you can use the following syntax. Use LOWER (or UPPER) with LIKE for case-insensitive pattern matching Of course, you can use `NOT LIKE` with any of the patterns we described. This is exactly equivalent to the syntax `WHERE title != ‘Staff’` SELECT DISTINCT For example, to find all titles except for Staff, we can use the syntax `WHERE title NOT LIKE ‘Staff’` What if you want to find all rows that do not match a specific criterion? You can use the NOT LIKE operator. Use NOT to find strings that do not match a pattern The pattern ‘%ann_’ matches a string that starts with any number of characters and ends with ‘ann’ and one other character.
Of course, you can use both ‘%’ and ‘_’ to create interesting patterns. Use both ‘%’ and ‘_’ to match any pattern What are the different names with only three characters? We can find out using three consecutive underscores _ as the pattern. How many ways are there to spell Le_n? The pattern would match anything from ‘Lexn’, ‘LeAn’, Le3n’, or ‘Le-n’. Like the game Hangman, the underscore sign _ can only fit one character. Use ‘_’ to match one (and only one) character For example, if you want to find names that contain z, use ‘%z%’. You can also use multiple ‘%’ in one pattern. To find names that end with ’Z’, try the pattern ‘%z’. Suppose you want to find all employees whose name starts with ‘Adam’ you can use the pattern ‘Adam%’ SELECT DISTINCT ‘%’ can be used to match any (even zero) number of characters – a number, an alphabet, or a symbol. Use ‘%’ to match any number of characters WHERE first_name LIKE 'Barry' - the same as WHERE first_name = ‘Barry’ 2. If you’d like to perform an exact string match, use LIKE without ‘%’ or ‘_’ SELECT Beginner SQL LIKE Examplesīelow, we’ve outlined some practical examples of how you can use the LIKE statement and the results from our sample data set. You can use LIKE to achieve a variety of pattern-matching. You could use the underscore sign `_` too. The `%` is not the only wildcard you can use in conjunction with the LIKE operator. The magic here is in the clause `WHERE first_name LIKE ‘A%’`, which means “find all first_name starting with A and ending with any number of characters.” The `A%` here is known as a pattern for matching. But why would you do that when you have the LIKE operator? SELECT DISTINCT You could spend time looking through the table manually. Suppose you have an employees table and would like to find all names that start with ‘A’. If you learn better with hands-on practice, you can also follow along (and run the code) on this DataCamp Workspace.
This article provides a quick tutorial on LIKE for beginners and intermediates. It gives data practitioners the power to filter data on specific string matches. Like it or not, the LIKE operator is essential in SQL. The SQL LIKE Operator for Pattern Matching
0 kommentar(er)
