AppDividend
Latest Code Tutorials

SQL Wildcard Characters Example | SQL Wildcard Operators Tutorial

0

SQL Wildcard Characters Example | SQL Wildcard Operators Tutorial is today’s topic. 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 like operator is used with where clause for searching some specified pattern from a column. If you are unfamiliar with SQL datatype then check out my SQL Datatypes tutorial on this blog.

SQL Wildcard Characters

The Types of SQL wildcard operators are 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

This operator 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

Emp_id Emp_name City State Salary
101 Rohit Patna Bihar 30000
201 Shivam Jalandhar Punjab 20000
301 Karan Allahabad Uttar Pradesh 40000
401 Suraj Kolkata West Bengal 60000
501 Akash Vizag Andhra Pradesh 70000

 

Example 1

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

Output

 

SQL Wildcard Characters

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

 

SQL Wildcard Operators

Explanation

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

Example 3

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

Output

 

SQL Wildcard Characters Example

Explanation

The above query resulted in all the details of an employee whose city name have 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

Emp_id Emp_name City State Salary
101 Rohit Patna Bihar 30000
201 Shivam Jalandhar Punjab 20000
301 Karan Allahabad Uttar Pradesh 40000
401 Suraj Kolkata West Bengal 60000
501 Akash Vizag Andhra Pradesh 70000

 

Example 1

Select * from employee where city like '_atna';

Output

 

SQL Wildcard Operators Tutorial

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:

 

SQL Wildcard

Explanation:

The following SQL statement selects all type of employees whose city starts with Kol followed by any character than a followed by a character then at last ends with a.

Operations performed by both % and _ operator

Table_1: Employee

Emp_id Emp_name City State Salary
101 Rohit Patna Bihar 30000
201 Shivam Jalandhar Punjab 20000
301 Karan Allahabad Uttar Pradesh 40000
401 Suraj Kolkata West Bengal 60000
501 Akash Vizag Andhra Pradesh 70000

 

Example 1

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

Output

 

Operations performed by both % and _ operator

Explanation

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

Example 2

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

Output

 

% and _ operator

Explanation

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

[ char list] operator

The char list operator is used for representing any single character within the brackets.

Suppose, if we declare h[ao]t in a query then 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 with more example.

Consider the following table.

Table_1: Employee

Emp_id Emp_name City State Salary
101 Rohit Patna Bihar 30000
201 Shivam Jalandhar Punjab 20000
301 Karan Allahabad Uttar Pradesh 40000
401 Suraj Kolkata West Bengal 60000
501 Akash Vizag Andhra Pradesh 70000

 

Example 1

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

Output

Emp_id Emp_name City State Salary
101 Rohit Patna Bihar 30000
201 Shivam Jalandhar Punjab 20000
301 Karan Allahabad Uttar Pradesh 40000

 

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

Emp_id Emp_name City State Salary
401 Suraj Kolkata West Bengal 60000
501 Akash Vizag Andhra Pradesh 70000

 

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

Emp_id Emp_name City State Salary
101 Rohit Patna Bihar 30000
501 Akash Vizag Andhra Pradesh 70000

 

Explanation

In this query details of all employee are 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

Symbol Description Example
* Represents zero or more characters bl* finds bl, black, blue, and blob.
? Represents a single character h?t finds a hot, hat, and hit.
[] Represents any single character within the brackets h[oa]t finds hot and hat, but not hit.
! Represents any character, not in the brackets h[!oa]t finds hit, but not hot and hat.
Represents a range of characters c[a-b]t finds cat and cbt.
# Represents any single numeric character 2#5 finds 205, 215, 225, 235, 245, 255, 265, 275, 285, and 295.

#Wildcard Characters in SQL Server

Symbol Description Example
% Represents zero or more characters. bl% finds bl, black, blue, and blob.
_ Represents a single character. h_t finds a hot, hat, and hit.
[] Represents any single character within the brackets. h[oa]t finds hot and hat, but not hit.
^ Represents any character, not in the brackets. h[^oa]t finds hit, but not hot and hat.
Represents a range of characters. c[a-b]t finds cat and cbt.

All the wildcards can also be used in combinations!

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

LIKE Operator Description
WHERE CustomerName LIKE ‘a%’ Finds any values that start with “a”.
WHERE CustomerName LIKE ‘%a’ Finds any values that end with “a”.
WHERE CustomerName LIKE ‘%or%’ Finds any values that have “or” in any position.
WHERE CustomerName LIKE ‘_r%’ Finds any values that have “r” in the second position.
WHERE CustomerName LIKE ‘a_%_%’ Finds any values that start with “a” and are at least 3 characters in length.
WHERE ContactName LIKE ‘a%o’ Finds any values that start with “a” and ends with “o”.

 

Finally, SQL Wildcard Characters Example | SQL Wildcard Operators Tutorial is over.

Leave A Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.