Advanced use of the LIKE operator in SQL

Advanced use of the LIKE operator in SQL.

Here is the SQL that goes with the demo.

-- Database by Doug
-- Douglas Kline
-- 5/19/2016
-- LIKE, advanced

use northwind

-- some advanced things to do with LIKE
-- if you are new to using LIKE, see my earlier video
-- previous video covers simple wildcard matching with % and _

-- we'll cover character sets
-- and specific use cases, i.e., how to do....

-- 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

-- what about begins with A or B or C
SELECT   ProductName,
         ProductID,
         Unitprice
FROM     Products
WHERE    ProductName LIKE 'a%'
  OR     ProductName LIKE 'b%'
  OR     ProductName LIKE 'c%'
ORDER BY ProductName

-- can also be phrased as

SELECT   ProductName,
         ProductID,
         Unitprice
FROM     Products
WHERE    ProductName LIKE '[abc]%'
ORDER BY ProductName

-- the square brackets mean:
-- "a single character from within this set"

SELECT   ProductName,
         ProductID,
         Unitprice
FROM     Products
WHERE    ProductName LIKE '[abc][lho]%'
ORDER BY ProductName

-- this one means
-- first letter is from this set: [abc]
-- second letter is from this set: [lho]
-- then zero or more characters after the first two letters

-- this can also be phrased as a "list", like this:

SELECT   ProductName,
         ProductID,
         Unitprice
FROM     Products
WHERE    ProductName LIKE '[a-c][lho]%'
ORDER BY ProductName

-- the hyphen means,
-- all characters alphabetically between the two letters
-- what about this?

-- products whose name does not start with
-- an a or b or c
SELECT   ProductName,
         ProductID,
         Unitprice
FROM     Products
WHERE    NOT   (ProductName LIKE 'a%'
            OR  ProductName LIKE 'b%'
            OR  ProductName LIKE 'c%')
ORDER BY ProductName

-- another option:

SELECT   ProductName,
         ProductID,
         Unitprice
FROM     Products
WHERE    ProductName NOT LIKE '[abc]%' -- using NOT
ORDER BY ProductName

-- can be rephrased like this:
SELECT   ProductName,
         ProductID,
         Unitprice
FROM     Products
WHERE    ProductName LIKE '[^abc]%'
ORDER BY ProductName

-- note the carat symbol ^ inside the brackets
-- this means
-- "any single character not in this list"

-- and this:
SELECT   ProductName,
         ProductID,
         Unitprice
FROM     Products
WHERE    ProductName LIKE '[^abc][^lho]%'
ORDER BY ProductName

-- this one means
-- first letter is NOT from this set: [abc]
-- second letter is NOT from this set: [lho]
-- then zero or more characters after the first two letters

-- another 
SELECT   ProductName,
         ProductID,
         Unitprice
FROM     Products
WHERE    ProductName LIKE '[a-ce-j]%' -- skips d
ORDER BY ProductName
-- means single letter in the set of letters
-- a thru c OR e thru j
-- then zero or more letters

-- so let's go through some common uses

-- number of letters
-- has at least 6 characters
SELECT   'match'   as [test]
WHERE    '1234567' LIKE '______%' -- 6 underscores

SELECT   'match'   as [test]
WHERE    '12345'   LIKE '______%' -- 6 underscores

-- begins with a letter
SELECT   'match'
WHERE    'product' LIKE '[a-z]%'

SELECT   'match'
WHERE    '3product' LIKE '[a-z]%'


-- begins with a letter
-- and is at least 6 characters
SELECT   'match'
WHERE    'a12345' LIKE '[a-z]_____%' -- 5 underscores

SELECT   'match'
WHERE    'a1234' LIKE '[a-z]_____%' -- 5 underscores

SELECT   'match'
WHERE    '012345' LIKE '[a-z]_____%' -- 5 underscores

-- begins with a number
SELECT   'match'
WHERE     '0abcdefgh' LIKE '[0-9]______%' -- 6 underscores


-- begins with a letter, contains a number
SELECT   'match'
WHERE    'abc5' LIKE '[a-z]%[0-9]%'

-- ending / trailing spaces

SELECT   'match'
WHERE    ' abc' LIKE ' %'

-- ending / trailing punctuation

SELECT   'match'
WHERE    '?abc345' LIKE '[!@#$%?,.]%'

-- whitespace char, i.e., tab

-- note that char(9) returns the ASCII tab character
-- also note that + is the concatenation operator
-- thus the format string is square brackets
-- with a space and a tab character
-- it checks for tabs or spaces

SELECT   'match'
WHERE    'db' + char(9) + 'Doug' LIKE '%[ ' + char(9) + ']%'

-- detect punctuation character
-- ASCII has 
SELECT   'match'
WHERE    'abc{abc' 
   LIKE  '%[' 
         + char(33) + '-' + char(47) -- ASCII '!' thru '/'
         + char(58) + '-' + char(64) -- ASCII ':' thru '@'
         + char(91) + '-' + char(96) -- ASCII '[' thru '`'
         + char(123)+ '-' + char(126)-- ASCII '{' thru '~'
         + ']%'

SELECT   'match'
WHERE    'abc]abc' 
   LIKE  '%[' 
         + char(33) + '-' + char(47) -- ASCII '!' thru '/'
         + char(58) + '-' + char(64) -- ASCII ':' thru '@'
         + char(91) + '-' + char(96) -- ASCII '[' thru '`'
         + char(123)+ '-' + char(126)-- ASCII '{' thru '~'
         + ']%'


-- contains unprintable characters
SELECT   'match'
WHERE    'abc'
         + char(7) -- bell character
         + 'abc' 
   LIKE  '%[^'  -- note the carat here
         + char(32) + '-' + char(126)
         + ']%'

-- same technique can be used for whitespace characters
-- same technique can be used for unicode character set

-- Database by Doug
-- Douglas Kline
-- 5/19/2016
-- LIKE, advanced