SQL

Moar About SQL Wildcards

February 8, 2016

Previously we looked at using wildcards in conjunction with the SQL LIKE operator. Wildcards allow you to search for data in a defined table. Think of them as text globbing for SQL. The wildcards available include the following:

  1. [list]: Define a ranges of characters for pattern matching
  2. [!charlist]: Matches only a character NOT specified within the brackets
  3. %: Require a single character/object in a pattern
  4. _: Allow any single character in a pattern

In this article, we’ll use the same “Customers” table from our first articles:

ID Site Contact Address City Zip Country
1 Krypted Charles Edge my house Minneapolis 55418 US
2 Apple Tim Cook spaceship Cupertino 95014 US
3 Microsoft Satya Nadella campus Redmond 98053 US
4 Facebook Mark Zuckerberg foodhall Menlo Park 94025 US
5 JAMF Dean Hager Grain Exchange Minneapolis 55410 US

The following SQL statement selects all customers with a City starting with “Minne”:

SELECT * FROM Customers
WHERE City LIKE 'Minne%';

The above SELECT would not locate an object that was just called Minne because the % indicates that something must be there. We could also look for something with nn but with something before and after the pattern:

SELECT * FROM Customers
WHERE City LIKE '%nn%';

Or to look for something that would contain Minneapoli:

SELECT * FROM Customers
WHERE City LIKE 'Minneapoli_';

Not that the _ is looking for a single character and that the % is looking for any string in that space. We could also look for a set of objects or omit a set of objects using brackets to define multiple items or a range. For example, let’s say we wanted to look for zip codes 55418 and 55410

SELECT * FROM Customers
WHERE Zip LIKE '[55418 55410]%';

Or 55410 through 55419, use a dash to separate the two (in ascending order):

SELECT * FROM Customers
WHERE Zip LIKE '[55410-55418]%';

Or the ones that don’t match that pattern:

SELECT * FROM Customers
WHERE Zip LIKE '[!55410-55418]%';

Overall, the globbing/pattern matching options are very basic (think DOS-like syntax) unless you use more complicated functions. But, it’s amazing what kind of stuff you can string together with simple commands, joins, and other tricks, without having to get into functions.