LIKE in SQL, Explained for Beginners

By Michael Nocito, data analyst · Updated August 19, 2026 Most of the time you ask SQL for an exact value. WHERE status = 'Active' is a closed question with a yes or no answer. But a lot of real columns do not hold one tidy value. They hold a sentence, a product name with a code stuck on the front, an email address, or a list of tags jammed into a single cell. For those you need a looser question, and LIKE is how you ask it. It is a small piece of syntax with exactly two moving parts, and once those click you can search inside text instead of only matching it. The one-sentence version. = asks "is this value exactly this?" LIKE asks "does this value match this shape?" The shape is written with two wildcards: % stands for any run of characters including none, and _ stands for exactly one character. Why equals asks a stricter question than you want Take a products table with a name column: id name 1 Blue Cotton Shirt 2 Shirt Stay Clips 3 Red Wool Sweater 4 Shirt You want the shirts. The obvious thing fails: SELECT name FROM products WHERE name = 'Shirt'; That returns one row, id 4, because = compares the whole value start to finish. Blue Cotton Shirt is not the same string as Shirt, so it is excluded, and correctly so. Equals is doing its job. It is just the wrong question. The question you actually have is "does the name contain the word Shirt anywhere in it?" That is a pattern, and patterns are what LIKE is for: SELECT name FROM products WHERE name LIKE '%Shirt%'; Three rows come back: Blue Cotton Shirt, Shirt Stay Clips, and Shirt. The two wildcards, % and _ Everything LIKE can do comes from two characters. Wildcard Means Example Matches % Any run of characters, including none at all 'S%' S, Sam, Sweater _ Exactly one character, no more, no fewer 'S_m' Sam, Sum, but not Steam The "including none at all" part of % is the bit people miss. 'S%' matches the single letter S, because % is allowed to stand for nothing. The underscore is not so forgiving. It demands a character be there. Say this one out loud before you read the answer: what would '_at' match, and would flat be in the list? Commit to an answer first. It matches three-character values ending in at, so cat, hat, bat. flat is out, because _ covers one character and flat has two before the at. If you wanted both you would write '%at'. You can mix them. 'A__-%' matches a value that starts with A, has exactly two more characters, then a hyphen, then anything. That is a useful shape for product codes and account numbers, where the format is fixed but the content is not. Where you put the % is the whole question Same wildcard, four positions, four completely different questions. This is the table worth memorising: You write You are asking Matches "Blue Cotton Shirt"? LIKE 'Shirt' Is it exactly Shirt (same as =) No LIKE 'Blue%' Does it start with Blue Yes LIKE '%Shirt' Does it end with Shirt Yes LIKE '%Cotton%' Does it contain Cotton anywhere Yes A LIKE pattern with no wildcard in it is just a slower =. If you write LIKE 'Shirt' you have asked for an exact match and should use = instead, which is clearer to whoever reads the query next. Read the pattern left to right, in words. '%Cotton%' reads as "anything, then Cotton, then anything." 'Blue%' reads as "Blue, then anything." If you can say the pattern as a sentence, you will not put the percent signs in the wrong place. NOT LIKE, and finding what is missing Put NOT in front and you get the opposite set: SELECT name FROM products WHERE name NOT LIKE '%Shirt%'; That returns the rows with no Shirt in them. This is more useful than it first looks, because "which rows do not fit the expected shape" is one of the most productive questions in data cleaning. Emails with no at sign, product codes missing their prefix, a text column where some rows hold a list and some do not. NOT LIKE finds the odd ones out. One catch worth knowing now. A row where the column is NULL is returned by neither LIKE nor NOT LIKE. NULL means unknown, and SQL will not claim an unknown value matches or fails to match a pattern. If you want the blanks too, ask for them: WHERE name NOT LIKE '%Shirt%' OR name IS NULL. The full story is in NULL in SQL. Case sensitivity, which changes by database Whether '%shirt%' finds Blue Cotton Shirt depends on which database you are sitting in. This surprises people who move between two of them, so it is worth checking once rather than assuming. Database Is LIKE case sensitive? How to force insensitive SQLite No for plain A-Z letters, yes for accented and non-English ones Already insensitive for A-Z PostgreSQL Yes Use ILIKE MySQL Depends on the column collation, usually no Usually already insensitive SQL Server Depends on the collation, usually no Usually already insensitive The portable move, if you want the same answer everywhere, is to flatten both sides yourself: SELECT name FROM products WHERE LOWER(name) LIKE '%shirt%'; That works in every database listed above, at the cost of a little speed. See the last section for why. The over-matching trap Predict this one before reading on. You search a genre column with LIKE '%Video%'. What comes back? In a real Steam games dataset, that pattern matches both Video Production and 360 Video, because both contain those five letters. That may be exactly what you wanted, or it may quietly double your result set. % does not know about word boundaries. It matches letters, not meaning. Some classics: Pattern You meant You also get '%art%' art cart, start, Bart, particle '%man%' man manage, human, Germany '%IT%' the IT department credit, exit, monitor (where LIKE is case insensitive) The fix is to give the pattern more to hold on to. If the values are separated by a known character, include it: '%, Sales%' is far more precise than '%Sales%'. The next section shows the reliable version of that trick. Searching a column that holds a list This is where analysts reach for LIKE most often. Some columns pack several values into one cell, separated by commas: name genres Game A Action,Indie,RPG Game B Casual,Indie Game C RPG = is useless here, because almost no cell holds only RPG. LIKE handles it without any splitting: SELECT name FROM games WHERE genres LIKE '%RPG%'; That finds Game A and Game C, whether the tag sits first, last, or buried in the middle. The precise version guards against the over-matching trap. Wrap the value in the separator on both sides, then search for the separated tag: SELECT name FROM games WHERE ',' || genres || ',' LIKE '%,RPG,%'; The || glues text together, so Action,Indie,RPG temporarily becomes ,Action,Indie,RPG,. Now every tag has a comma on both sides, including the first and last, and the pattern '%,RPG,%' can only match a whole tag. A genre called RPGMaker would no longer be a false hit. Use CONCAT(',', genres, ',') instead of || in MySQL and SQL Server. That column shape causes more trouble than just searching, including a distinct count that comes back wrong and never says so. One Cell, Many Values covers the whole problem. Matching a literal % or _ Every so often the character you are hunting for is a wildcard. Searching a notes column for an actual percent sign with LIKE '%%%' matches every row, which is not useful. Declare an escape character and mark the literal one: SELECT note FROM feedback WHERE note LIKE '%!%%' ESCAPE '!'; Read that pattern as: anything, then a real percent sign (the one after the !), then anything. The ESCAPE '!' at the end is what tells SQL that ! is the marker. You can pick any character for the job as long as it does not appear in your data. The same applies to a literal underscore, which matters more than you would think, because underscores are everywhere in column names and product codes. Why LIKE '%text%' gets slow An index on a text column works like the index at the back of a book: entries sorted alphabetically, so the database can jump straight to the right place. That works when you know how the value starts. Pattern Can an index help? Why 'Blue%' Yes Everything starting with Blue is filed together '%Shirt' No The start is unknown, so there is nowhere to jump to '%Cotton%' No Same problem, every row has to be read and checked On a few thousand rows this is invisible. On tens of millions it is the difference between an instant answer and a coffee break. It is not a reason to avoid '%text%', it is a reason to know why the query got slow when the table grew. If you need fast searching inside text at scale, that is a full text search index, a different tool. Indexing for Analysts covers what an index can and cannot do. The same logic explains the cost of LOWER(name) LIKE ... from earlier. Wrapping the column in a function means the index on that column no longer applies, because the index stores the original values, not the lowercased ones. A LIKE cheat sheet You want… Write Contains a word LIKE '%word%' Starts with LIKE 'word%' Ends with LIKE '%word' Exactly this = 'word', not LIKE Does not contain NOT LIKE '%word%' Exactly one unknown character LIKE 'A_C' A fixed format, like three letters then a dash LIKE '___-%' Case insensitive everywhere LOWER(col) LIKE '%word%' Case insensitive in PostgreSQL ILIKE '%word%' One whole tag from a comma separated list `',' A literal percent sign {% raw %}LIKE '%!%%' ESCAPE '!' Say it out loud before you run it. Read your pattern left to right as a sentence, swapping "anything" in for every %. If the sentence you say is not the question you meant to ask, the percent signs are in the wrong place. That one habit prevents most LIKE mistakes. More detail on this, and more like it. Every how-to sits in one place on the guides index: SQL, Tableau, data migration, and the working habits around them. Originally published on Analyst Prep Kit: LIKE in SQL, Explained for Beginners Visit the site for more beginner data analysis guides and free resources: the full guide archive covers SQL, Excel, Power BI, Tableau, Python and statistics, and the practice kits run in your browser with nothing to install. If it was useful: Buy Me a Coffee.