Wildcard in SQL

Overview of Wildcard in SQL

A wildcard character in SQL does the job of replacing in a string, zero to any number of characters. Usually, these wildcard characters can be found being used with the SQL operator LIKE. This is an operator that is commonly utilized in the WHERE clause of SQL, to hunt for a particular arrangement of characters.

Wildcards have a similar goal as that of Regular Expressions. A wildcard character is a substitute character that is used to replace specific other characters or characters in that string. These wildcards are beneficial when we want to accomplish a quick search for in the database.

We have two common wildcard characters in SQL. The percent symbol signifies zero, one or any number of characters. The underscore symbol denotes a single character, either a letter or a number. These signs can be combined in several arrangements. We also have [charlist], [^charlist] and [!charlist], which can be employed in both SQL and MS Access.

We will look at these wildcard characters in detail, along with a few other useful wildcard characters.

Wildcard	Description	Example
Percent sign: %	This wildcard matches zero or more characters	ha% finds ha, hat, happy, and happen
Underscore: _	This wildcard matches only a specific single character	p_t find pot, pit, and put
Square brackets: []	This wildcard matches a solo character specified within the brackets	p[oi]t find pot and pit, but not put
Caret: ^	This wildcard matches characters which are not present in the bracket after this symbol	p[^oi]t finds put, but not pot and pit

Let us note that MS Access uses the character asterisk symbol (*) for the wildcard in place of the percent symbol (%) wildcard character to match zero or more characters and it uses the character question mark (?) for the wildcard in place of the underscore wildcard character to represent a single character.

Syntax of SQL Wildcards

Let us look at the number of ways the Wildcard character ‘%’ and ‘_’ can be written:

SELECT FROM table_name
WHERE column LIKE 'AAAA%'

Or

SELECT FROM table_name
WHERE column LIKE '%AAAA%'

Or

SELECT FROM table_name
WHERE column LIKE ‘_AAAA'

Or

SELECT FROM table_name
WHERE column LIKE 'AAAA_’

Or

SELECT FROM table_name
WHERE column LIKE '_ AAAA _'

AAAA% helps us to look for any strings beginning with AAAA and ending there or ending with another single character or more than a single character.

%AAAA% helps us to look for any strings beginning with any number of characters but containing the string AAAA in between, and ending with any number of characters from 0 to infinity.

_AAAA helps us to look for any strings beginning with a single distinct character and ending with the string AAAA.

AAAA_ helps us to look for any strings beginning with AAAA pattern and ending with a single distinct character.

_AAAA_ helps us to look for any strings beginning with a distinct character, containing the pattern AAAA in between and ending with a single distinct character.

Examples for Each Wildcard Character in SQL

Here are some of the Wildcard Character examples given below

1) Working with the % Wildcard

With this example, we can select all the employees of a city starting with “the”:

SELECT * FROM Employees
WHERE City LIKE 'the%';

2) Working with the _ Wildcard

With this example, we can select all the employees of a city starting with a single distinct character, but ending with “elhi”:

SELECT * FROM Employees
WHERE City LIKE '_elhi';

Let us look at another example where we can select Employees of the city whose name starts with “B”, followed by a single distinct character, followed by “r”, followed by a single distinct character, followed by “in”:

SELECT * FROM Employees
WHERE City LIKE 'B_r_in';

3) Combining % and _ Wildcard

We can combine both the wildcard characters to optimize our searching process.

With this example, we can find any string having ‘a’ in the second position.

WHERE StringName LIKE '_a%'

With this example, we can find any string beginning with ‘x’ and having the length of a minimum of three characters.

WHERE StringName LIKE 'x_%_%'

With this example, we can find any string beginning with ‘x’ and ending with ‘y’, with a minimum one character in between.

WHERE StringName LIKE 'x%_y'

4) Working with the [] Wildcard

With this example, we can select all the employees of a city whose name begins with either “a”, “b” or “c”.

SELECT * FROM Employees
WHERE City LIKE '[abc]%';

We can write this same example in another way, by using the hyphen (-) symbol. This statement will also select all the employees of a city whose name begins with either “a”, “b” or “c”. It can be written as follows:

>SELECT * FROM Employees
WHERE City LIKE '[a-c]%';

5) Working with the [! ] Wildcard

With this example, we can select all the employees of a city whose name does NOT begin with either “a”, “b” or “c”.

SELECT * FROM Employees
WHERE City LIKE '[!abc]%';

This statement can also be written as follows:

SELECT * FROM Employees
WHERE City NOT LIKE '[abc]%';

Conclusion

In this SQL Wildcard article, we have looked at all the wildcards in SQL. Furthermore, we looked at various examples of the SQL Wildcard Characters to give us more understanding and clarity.

To conclude, Wildcard characters do similar work as that of Regular Expressions. We can combine multiple wildcards together in a solo string to obtain better search outcomes and results. There can be a few databases like MS Access that could use a separate SQL wildcard for a similar function.

Recommended Articles

This is a guide to Wildcard in SQL. Here we discuss the Syntax of SQL Wildcards with the examples for each Wildcard Character. You may also look at the following article to learn more –

1. SQL Views
2. SQL Management Tools
3. What is MySQL?
4. Introduction to MySQL