SQL Wildcard

SQL wildcard is used to match on a string pattern with the SQL LIKE operator when we search for data in a database.

Wildcards we can use with SQL:

Wildcard Description
% Zero, one, or more characters
_ Exactly one character
[charlist] Any single character in charlist
[^charlist] or [!charlist] Any single character NOT in charlist

Note: [charlist], [^charlist] and [!charlist] do not work properly in MySQL.

SQL Wildcard Example

Example 1
Table: Employees

EmployeeId FirstName LastName Department Salary
203 Mazojys Fxoj Finance 78000
204 Jozzh Lnanyo Finance 45800
205 Syllauu Dfaafk Finance 55000
206 Gecrrcc Srlkrt Finance 75000
302 Jssme Bdnaa Development 75000
303 Dnnaao Errllov Development 55000
304 Tyoysww Osk Development 49000

Select all employees whose first name start with ‘Ma’:

SELECT * FROM Employees
WHERE FirstName like 'Ma%'

The result will look like:

EmployeeId FirstName LastName Department Salary
203 Mazojys Fxoj Finance 78000

Example 2
Some examples of match patterns:

Pattern Strings
B_T String that has 3 characters – starts with ‘B’, another character, end with ‘T’. For example, ‘BaT’, ‘BHT’, ‘BET’ satisfy the pattern, while ‘BaaT’, ‘BOAT’ would not (because there are two characters between B and T instead of one).
AB% String that starts with ‘AB’. For example, ‘ABC’, ‘ABCD’, ‘AB123′ satisfy the pattern.
%OR String that ends with ‘OR’. For example, ‘DOCTOR’, ‘AUTHOR’ satisfy the pattern.
%OO% String that contains ‘OO’ anywhere. For example, ‘GOOD’, ‘TOO’, ‘LOOK’, ‘OOPS’ satisfy the pattern.
%R_N% String that contains 3 characters which start with ‘R’, another character, end with ‘N’. For example, ‘PRINCE’, ‘PRINT’, ‘GRAND’, ‘RUN’, ‘CATHARINES’ satisfy the pattern.
[AB]% String that starts with ‘A’ or ‘B’. For example, ‘APPLE’, ‘AIR’, ‘BROTHER’, ‘BLUE’ satisfy the pattern.
[!AB]% String that does not start with ‘A’ or ‘B’. For example, ‘GOOD’, ‘WORLD’ satisfy the pattern.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>