SQL Wildcard

In this tutorial, we will learn about the Wildcard characters of SQL.

SQL Wildcard characters

Wildcard characters are used to substitute any other characters in a string.

Wildcard characters are used with the SQL LIKE operator with the WHERE clause and can be used to search data of a specified pattern.

The % (Percent Sign) and _ (Underscore) sign both can also be used in combinations.

In SQL, Wildcards are as follow:

Example:

Let us consider this table "Employee" for records.

Table Name: Employee

Example of LIKE Operator

Here is an example of LIKE operator where an employee name starting with "S", aquery will be:

SELECT * FROM Employee
WHERE EmpName LIKE 'S%';

Here is another example of LIKE operator where an employee name ending with "%h".  a query will be:

SELECT * FROM Employee
WHERE EmpName LIKE '%h';

Here is an another example of LIKE operator where an Employee Name that have "%ra%" in any position, query will be:

SELECT * FROM Employee
WHERE EmpName LIKE '%ra%';

Here is an example of LIKE operator where an employee name that has "a" at a third position, query will be:

SELECT * FROM Employee
WHERE EmpName LIKE '__a%';

Here is an example of LIKE operator where an employee name that starts with "s" and ends with "h", a query will be:

SELECT * FROM Employee
WHERE EmpName LIKE 's%h';

Here is an example of LIKE operator where an employee name that not starts with "s",  a query will be:

SELECT * FROM Employee
WHERE EmpName NOT LIKE 's%';

Example Using the [charlist] Wildcard With LIKE Operator

Here is an example of LIKE operator where an employee name that  starts with "r", "a" or "k", a query will be:

SELECT * FROM Employee
WHERE EmpName LIKE '[rak]%';

Example Using the [!charlist] or [^charlist] Wildcard

Here is an example of LIKE operator where an employee name that not starts with "r", "a" or "k", a query will be:

SELECT * FROM Employee
WHERE EmpName NOT LIKE '[rak]%';

OR you can write like this:

SELECT * FROM Employee
WHERE EmpName LIKE '[!rak]%';