Which wildcard character represents any number of characters at the specified position?

Last updated on September 13th, 2022 at 03:38 pm

The like operator is used with the where clause for searching some specified pattern from a column. If you are unfamiliar with SQL datatype, check out my SQL Datatypes tutorial on this blog.

SQL wildcard characters are used for substituting one or more characters in a string. Wildcard operators, or you can say characters, are used with LIKE operators.

The Types of SQL wildcard operators are the following.

1. %
2. _
3. [char list]

The percent sign represents zero, one, or multiple characters. The underscore represents a single number or a character. These symbols can be used in combinations.

Let’s get in brief with all the characters one by one.

% operator in SQL

The % operator in SQL is used to find any string from a column whose complete string information is unknown to you.

Let’s understand this with an example.

Table_1: Employee

Example 1

Select * from employee where city like 'P%';

Output

Explanation

This query resulted in all the details of an employee whose city name starts with P.

Example 2

Select * from employee where city like '%a';

Output

Explanation

The above query resulted in all the details of an employee whose city name ends with a.

Example 3

select * from employee where city like '%p%';

Output

Explanation

The above query resulted in all the details of an employee whose city name has p in any position.

An _ operator in SQL

The _ operator is used as a substitute for one or more characters based on the number of times _ is used. Let’s understand this with an example.

See the following table.

Table_1: Employee

Example 1

Select * from employee where city like '_atna';

Output

Explanation

The above SQL statements select all employees whose city starts with any character followed by “atna”.

Example 2

select * from employee where city like 'Kol_a_a';

Output:

Explanation:

The following SQL statement selects all types of employees whose city starts with Kol followed by any character, then followed by a character, then ends with a.

Operations performed by both % and _ operator

Table_1: Employee

Example 1

Select * from employee where state like '_u%';

Output

Explanation

The above query will print the details of an employee whose state name 1st position starts with any character followed by u and rest any other characters.

Example 2

select * from employee where state like 'a_%_%';

Output

Explanation

The above query will print the details of an employee whose state name 1st position starts with A, followed by any other characters but should be of at least three characters in length.

[char list] operator

The char list operator represents any single character within the brackets.

Suppose, if we declare h[ao]t in a query, the resulting set will consist of either hat as output or hot as output but not hit as i is not present in the brackets.

Let’s clear this up with more examples.

Consider the following table.

Table_1: Employee

Example 1

select * from employee where city like '[PJA]%';

Output

Explanation

The above query will print the details of the employee whose city the first character either starts with ‘P’ or ‘J’ or ‘A’.

Example 2

select * from employee where city like '[!PJA]%';

OR

Select * from employee where city like ‘[^PJA]%’;

Output

Explanation

The above query will print the details of the employee whose city the first character does not start with ‘P’ or ‘J’ or ‘A’. Here this query was used for fetching non-matching set or range of characters specified inside the brackets.

Example 3

Select * from employee where city like ‘[P-Z]%’;

Output

Explanation

In this query, details of all employees have displayed whose city the first character lies in the range from P to Z. Note: P and Z are inclusive.

SQL Wildcard Characters

A wildcard character is used to substitute one or more characters in a string.

Wildcard characters are used with the SQL LIKE operator. The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.

Wildcard Characters in MS Access

Wildcard Characters in SQL Server

All the wildcards can also be used in combinations!

Here are some examples showing different LIKE operators with ‘%’ and ‘_’ wildcards:

That’s it for this tutorial.

Which wildcard character represents any number of characters?

Which wildcard operator can be used to take the place of a number of characters?

Which of the following is the wildcard used for any number of characters in SQL?

What shell wildcard can be used to match any character or number of characters?