Wild Card Operators In SQL

Let us talk about the Wild Card Operator in SQL. Wild Card seems to be the word that we hear in reality shows when someone is allowed to take part in a competition, even though they have not done this in the usual way. Well we are not talking about this Wild Card, we will be explaining Wild Card Operators in SQL. In SQL, wildcards are a specific pattern that go and search inside a string and fetches the records for the user. Wildcard characters can be used in "LIKE" expressions; the percent sign (%) matches zero or more characters and underscore (_) a single character.

For example I have a table called Department with the following data.


Figure 1.0 Department Records

I want to search records of the name Production, for that here's a query.

select * from HumanResources.Department where name='Production';

And the record is as follows: SQL has searched the record based on your search criteria and displayed on the screen.


Figure 1.1 Query Output

So all of you will be thinking, what's so difficult in this? We just need to search for the name that we want to search for.

Let us complicate our search criteria.


Figure 1.2 Table Records

Now I want to display the records of Production and Production Control, so if use the preceding query it won't be possible to get the records, now the Wild Card operators are useful to solve the problem. Wild Cards are specific patterns that go and search inside a string and fetch the records for the user.

For example:

select * from HumanResources.Department where name like 'Pro%'

Figure 1.3 Query Output

Now in the preceding query % is a Wild Card operator. In order to use the Wild Card operator we need to use like keyword in the query. The % sign signifies that the letters after Pro will be considered as it is and will not be considered as part of the criteria. So as you can see in the output, % signifies that it will search for the first three characters as the search criteria and then will not search, in other words it will show all the records starting with Pro that is a search criteria.

Now I want to display the records whose name's second character starts with A. So here the role of the Wild Card operator (_) comes can be used.


Figure 1.4 Table Records

select * from HumanResources.Department where name like '_a%'


Figure 1.5 Query Output

So in the preceding we can see that the query has displayed all the records starting with A as the second character and using (_) for the first letter it skipped and the second letter is searched (A). The other letters are not searched.

There is one more keyword or operator called square brackets, for example we want to display the records that have the letter "L" as in the following:


Figure 1.6 Table Records

Example

select * from HumanResources.Department where name like '%[l]%'

Figure 1.7 Query Output

So it has displayed:

Tool Design

• Sales
• Research and Development
• Production Control
• Document Control
• Quality Assurance
• Facilities and Maintenance

All the words that contain the letter "l" in it.

Now what if I want to display the words that don't have the letter "l" in them?


Figure 1.8 Table Records

Example

select * from HumanResources.Department where name not like '%[l]%'

Figure 1.9 Query Output

It searched for the L char in the Char list (entire word).

Now if we want to search for the Name with a starting character of P but the second character is not R.


Figure 2.0 Table Records

Example

select * from HumanResources.Department where name like 'P[^r]%'

Figure 2.1 Query Output

I hope this article will be helpful to easily understand the concept of Wild Cards. Whenever the interviewer asks you about the Wild Card operator don't be confused. They are talking about the preceding discussed search patterns. I can end up saying that when you can't remember exactly how it is spelled, try using a wildcard character in a query.