The SQL Operator LIKE is one of the most impressive parts of SQL. LIKE gives you the ability to search through a database for a particular value. When used in combination with SQL’s wildcards, you could potentially build a mediocre search engine. Of course, it would be wise to build an index first, but that’s more complicated stuff you don’t need to worry about just yet. LIKE is particularly useful for large content data types in SQL. Using like on numbers isn’t exactly a common practice.
SQL Wildcards
We really need to use wildcards for this tutorial, so let’s discuss the two common ones really quick
- % – any characters and any length
- _ – any characters, but only ONE character
We’ll see how to use these below, but basically you will just put them right before and/or after the value you are searching for.
Searching a Database Using SQL
Our table:
| id | content |
|---|---|
| 1 | I am some sample content |
| 2 | sam |
| 2 | This isn’t the best default text |
SELECT * FROM table_name WHERE content LIKE ‘%am%’
We were searching for am, and we didn’t care what came before or after it, which is why am had the wildcard, %, before and after it. You might have noticed that we didn’t have our equal operator. That’s because LIKE is an operator and it replaces the equals sign. While the record containing ‘sam’ didn’t have anything after it, SQL still matched it. This is because the SQL wildcard takes zero to unlimited characters. What if were were looking only for the names, Pam or Sam, but weren’t smart enough to use an OR? Simple:
SELECT * FROM table_name WHERE content LIKE ‘_am’
While this isn’t used very often, it is still useful to understand. The only difference in the two examples is that we removed the % wildcards and put a new wildcard, _, right in front of am. This wildcard just returns a match only if the entire record consists of 3 letters and the last 2 are am. Remember you can always put these wildcards before, after, or both around your search value and that it must be in quotes.