SQL Wildcard Characters and Their Usage

In this tutorial, you are going to learn how to use the wildcard characters  % and _ to search database tables in SQL Supported databases engines, such as Oracle and MS SQL Server.

1- The % character and SQL Queries

First, let’s learn how the % character help in querying for data. The character ‘%’ can be used to search any record from the database, so it takes the given letter and return the matched records in the query results.

Let say we want to find names that start with the letter f, we use 'f%' in the where clause, or if we’re going to search for a name that contains the letter f, we use '%f%'.

Example of the character % in SQL Queries

Example1

SELECT *
FROM emp
WHERE job LIKE 'gt%';

Returns all data from a table emp where job starts with the letters gt.

Example2

SELECT *
FROM emp
WHERE eName LIKE '%u';

Brings all data from a table emp where eName ends with u letter.

Example3

SELECT *
FROM emp
WHERE eName LIKE '%t%t%';

Returns all data from a table emp where eName has two t‘s in it.

2- The _ character and SQL Queries

Underscore ‘_ ‘ is used to find a single character in a particular position in a string. Let’s say we want to search for a name that has the letter ‘a’ in the second position. So we use: like _a%

Example of the character _ in SQL Queries

Example1

SELECT *
FROM emp
WHERE eName LIKE '__r%';

Returns all data from a table emp where eName has an r in the third position.

Example2

SELECT *
FROM employeeInfo
WHERE salary like '5__0'

This will return all employees whose salary starts with 5, ends with 0, and has 3 numbers in between.

Example3

SELECT * from employeeInfo
WHERE phoneNumber like '__2%'

This will returns all employees having  2 as the second digit in their phone numbers.

 

Leave a Reply

Your email address will not be published. Required fields are marked *