SQL Wildcard Characters

This is a detailed tutorial of SQL Wildcard Characters. Learn to use different wildcards to find matching pattern strings with the LIKE operator.

Table of Contents

Use of SQL Wildcard Characters

Wildcard characters are used in the SQL Like Operator to create different patterns for which you want to find the values in the database tables. A single wildcard may represent one or more characters in a string or value. Further, you might be aware of the fact that the LIKE operator is itself used in the SQL WHERE clause. Therefore, you will always find yourself using Wildcards in a WHERE clause with the LIKE operator.

Note. Different database software like MS Access, SQL Server, MySQL makes use of different wildcard characters for the same purpose. So, you might be clear of the fact that which characters mean what for which database software.

Wildcard Characters in SQL Server

The following table lists the different wildcard characters that you can use in SQL Server along with their description and an example for each.

Wildcard	Description	Example
%	To represent zero or more characters	a%b will fetch all records that start with a and ends with b
_	To represent a single character	a_b will fetch all records that only three characters in the string and the first and last character is a and b respectively.
[]	To represent any single character among different characters that are written in the brackets.	a[fk]b will fetch either afb or akb
^	Used within [] wildcard to find a single character that is not among the different characters written within the brackets.	a[^fk]b will fetch all of the strings that have only 3 characters, starts with a, end with b and in the center contains any other character except f and k. In other words, all three-letter strings that start with a and end with b except afb and akb.
–	To specify a range of characters and it is also used within square brackets wildcard.	a[c-f]b will fetch all 3 letter strings that start with a, end with b and in the center any character between c and f such as acb, adb, aeb, etc.

Wildcard Character in MS Access

Wildcard	Description	Example
*	Used for zero or more characters.	a*b will fetch any string that starts with a and ends with b.
?	Used for a single character	a?b will fetch any three-letter string that starts with a and ends with b.
[]	Used for selecting any single character among different characters that are written within brackets.	a[gh]b will fetch either agb or ahb.
!	Used within the wildcard [] to represent characters that are not written within brackets.	a[!gh] will fetch all three-letter strings that starts with a, ends with b except agb or ahb.
–	Used to specify a character range	a[o-r]b will fetch three-letter strings starting with a, ending with b and have their central character lying between o and r, such as aob, apb, aqb, etc.
#	Used to specify a single numerical character	1#3 will fetch any three-digit number that starts with 1 and ends with 3, such as 103, 113, 123, etc.

Note. You can always use multiple wildcard characters in combination to form patters as per your requirement.

Some of the wildcard combinations are given below.

Pattern	Description
LIKE ‘g%’	Filter all records that start with the character ‘g’
LIKE ‘%g’	Filter all records that end with the character ‘g’
LIKE ‘%g%’	Filter all records that contain the character ‘g’ anywhere in the full string
LIKE ‘_g%’	Filter all records that have the character ‘g’ in the second position.
LIKE ‘___g’	Filter all records that end with g and has string length 4.
LIKE ‘gu%t’	Filter all records that start with ‘gu’ and ends with ‘t’.
LIKE ‘g[a-m][^a-d]_%’	Filter all records the follow these string conditions: It starts with g. The second character lies between a and m. The third character does not lie between a and d. There is definitely the fourth character. The overall combination tells that there are at least four characters in the string as defined in the point 1, 2, 3 and 4 and there may more any more characters as % symbol defined in the end.

Note. MySQL only supports two wildcard chracters % and _.

Examples

We’ll illustrate the wildcard characters with example queries ran for MySQL database. You can run these on another database software the same way, it’s just that the symbols may change.

We’ll run the example queries on our demo database table employees, the screenshot of the table structure and some of its data is given below.

% Character

The following query selects all the records for which the column first_name starts with B and ends with D.

SELECT * FROM employees WHERE first_name LIKE 'B%D'

1	SELECT * FROM employees WHERE first_name LIKE 'B%D'

_ Chracter

The following query will fetch all the records for which the first_name has a string length of 5 characters and it also ends with D.

SELECT * FROM employees WHERE first_name LIKE '____D'

1	SELECT * FROM employees WHERE first_name LIKE '____D'

[] Character

The following query will fetch all the records for which the second character of the last_name column value is either A, B or C.

SELECT * FROM employees WHERE last_name LIKE '_[abc]%'

1	SELECT * FROM employees WHERE last_name LIKE '_[abc]%'

^ Character

The following query will fetch those records for which the third character of last_name column valye is not either a or b or c.

SELECT * FROM employees WHERE last_name LIKE '__[^abc]%'

1	SELECT * FROM employees WHERE last_name LIKE '__[^abc]%'

# Wildcard

The query is written below will select all those records for which the column emp_no has the first three digits 1, 2, 3 and 4.

SELECT * FROM employees WHERE emp_no LIKE 1234#

1	SELECT * FROM employees WHERE emp_no LIKE 1234#

– Character

This query will select all those records for which the first character of first_name lies between the range A to M.

SELECT * FROM employees WHERE last_name LIKE '[a-m]%'

1	SELECT * FROM employees WHERE last_name LIKE '[a-m]%'

Wild Characters Combination

We’ve tried to add every wildcard character in the following query. This query will fetch all those records that follow the conditions written below.

Conditions.

The first character can be anything.
The second character lies in the rage A to G.
The third character could be anything except o and q.
The fourth character is either m or g or p.
The string length is minimum 4 as the first characters are defined in conditions number 1, 2, 3 and 4 and the % wildcard character at the end represents zero or more chracters.

SELECT * FROM employees WHERE first_name LIKE '_[a-g][^oq][mgp]%'

1	SELECT * FROM employees WHERE first_name LIKE '_[a-g][^oq][mgp]%'

Note. The examples involving wildcard characters [], ^ and – do not run on MySQL Databases but works for SQL Server.

I hope you find this guide useful. If so, do share it with others who are willing to learn SQL. If you have any questions related to this article, feel free to ask them in the comments section.

Also, don’t forget to Subscribe to WTMatter!

SQL