The SQL LIKE Operator

A short video covering the LIKE operator in SQL.

Here is the SQL that goes with the demo.

-- Database by Doug
-- Douglas Kline
-- 5/11/2016
-- LIKE

use northwind

-- the LIKE statement is a logical operator that evaluates
-- if a set of characters matches a pattern
-- if it matches, it returns true
-- otherwise it returns false

-- since LIKE returns a logical value,
-- it is common to use LIKE in the WHERE clause, 
-- which expects logical expressions

-- if you don't know the WHERE clause,
-- check out another video on my channel

-- let's start with a simple example from the products table

SELECT   ProductName,
         ProductID,
         Unitprice
FROM     Products
ORDER BY ProductName

-- notice that there are 77 products

-- now, let's limit that to all the products
-- whose names start with the letter A

SELECT   ProductName,
         ProductID,
         Unitprice
FROM     Products
WHERE    ProductName LIKE 'a%'
ORDER BY ProductName

-- now all the products whose names end in A
SELECT   ProductName,
         ProductID,
         Unitprice
FROM     Products
WHERE    ProductName LIKE '%a'
ORDER BY ProductName

-- now all the products that have an A anywhere
SELECT   ProductName,
         ProductID,
         Unitprice
FROM     Products
WHERE    ProductName LIKE '%a%'
ORDER BY ProductName

-- and all the ones without an A anywhere
SELECT   ProductName,
         ProductID,
         Unitprice
FROM     Products
WHERE    ProductName NOT LIKE '%a%'
ORDER BY ProductName

-- as you can see, the % character is special
-- it's a "wildcard" character, i.e.,
-- it can "match" on characters

-- before we go on, the general format of the LIKE operator is

-- [characterExpression] LIKE [formatExpression]

-- the [characterExpression] is the part that will be tested
-- the [formatExpression] defines the "test", or the format
--     used to test the [characterExpression] 

-- in the example above:
 --  ProductName LIKE '%a%'
 -- ProductName is the [characterExpression]
 -- '%a%' is the [formatExpression]

 -- ProductName            LIKE   '%a%'
 -- [characterExpression]  LIKE   [formatExpression]
 -- 

 -- its best to think of LIKE as a comparison operator, 
 -- similar to <, >, <=, >=, !=, etc.
 -- e.g., WHERE age > 35
-- the expressions on the left and right are compared,
--  based on a set of rules

-- there are two main special characters for LIKE

-- special character #1: % can match on zero or more characters

-- for demonstration purposes
-- a record with the word Match! is shown if the WHERE
-- clause evaluates to true
 
SELECT 'Match!'
WHERE  1=1

SELECT 'Match!'
WHERE  1=2

-- some examples...

SELECT 'Match!'
WHERE  'Fred' LIKE 'F%'

-- yes, Fred starts with F
-- the F matches on F, 
--  and the % matches on 'red'

SELECT 'Match!'
WHERE  'Fred' LIKE 'S%' 

-- no Fred doesn't start with S

 -- note that LIKE is case insensitive
SELECT 'Match!'
WHERE  'Fred' LIKE 'f%'

SELECT 'Match!'
WHERE  'fred' LIKE 'F%'

-- what about this
SELECT 'Match!'
WHERE  'F' LIKE 'F%'

-- yes, it matches
-- in this case, the F matches on 'F'
-- and the '%' matches on '', i.e., nothing

-- again, % matches on *zero* or more characters

-- how about this one

SELECT 'Match!'
WHERE  'Fred' LIKE 'F%d'

-- must start with F and end with d, any number of characters

-- or this

SELECT 'Match!'
WHERE  'F' LIKE 'F%'

-- or this

SELECT 'Match!'
WHERE  'Fred' LIKE '%r%'

-- must contain an r, any number of characters

-- or this

SELECT 'Match!'
WHERE  'Fred' LIKE '%f%'

-- must contain an f, any number of characters

-- or this

SELECT 'Match!'
WHERE  'F' LIKE '%ed'

-- must end in ed, any number of characters

-- and of course, most characters are NOT special
-- and must match exactly

SELECT 'Match!'
WHERE  'Fred' LIKE 'Fred'

-- but with no special characters 
-- it may make more sense to do this:

SELECT 'Match!'
WHERE  'Fred' = 'Fred'

-- special character #2: _ (underscore) matches exactly one character

SELECT 'Match!'
WHERE  'Fred' LIKE '____' -- 4 underscores

-- must have exactly 4 characters

SELECT 'Match!'
WHERE  '1F7.' LIKE '____' -- 4 underscores

-- must have exactly 4 characters

SELECT 'Match!'
WHERE  'Fred' LIKE '___' -- 3 underscores

-- must have exactly 3 characters

SELECT 'Match!'
WHERE  'Fred' LIKE 'F___' -- 3 underscores

-- must start with F, and have exactly 4 characters

SELECT 'Match!'
WHERE  'Fred' LIKE 'F__d' -- 2 underscores

-- must start with F, end in d, and have exactly 4 characters

-- combining the special characters

SELECT 'Match!'
WHERE  'Frederick' LIKE 'F__%' -- 2 underscores

-- must start with F and have at least 3 characters

SELECT 'Match!'
WHERE  'Frederick' LIKE '%_d_%'

-- must contain a d
-- d must not be the first or last letter
-- implies at least 3 characters

-- in summary
-- LIKE is a comparison operator
-- is compares a character string to a format string
-- % is a special character that matches on zero or more characters
-- _ is a special character that matches on exactly one character
-- other characters must match exactly
-- remember, digits and most punctuation are characters

-- thanks for watching

-- Database by Doug
-- Douglas Kline
-- 5/11/2016
-- LIKE