SQL Wildcards

The SQL WILDCARDS are used along with the SQL LIKE operator in the SELECT statement. The WILDCARDS are quite complex but are very useful in retrieving data. Consider if there’s no wildcards then we could not search in the database easily. You would have to enter each and every character to match the required result. So, for easier search, SQL WILIDCARDS comes to rescue.

Now what wildcards really are? They are set of characters which lets the DBMS to know that what and how to look for the query.

Some Important Wildcards

Wildcard	Description	Usage
%	This wildcard is used to ignore 1 or n character(s) before or after the condition	"%somecharacter", "somecharacters%" OR "%somecharacters%"
_	This wildcard is used to ignore 1 a single character before or after the condition	"_somecharacter", "somecharacters_" OR "_somecharacters_"
[mutliplecharacters]	This wildcard is used to search for multiple choices	"[somecharacter]", "[somecharacters]_" ,"_[somecharacters]_","%[somecharacter]", "[somecharacter]%" OR "%[somecharacter]%"
[!mutliplecharacters]	This wildcard is used to search for choices that are not in the given	"[!somecharacter]", "[!somecharacters]_" ,"_[!somecharacters]_","%[!somecharacter]", "[!somecharacter]%" OR "%[!somecharacter]%"

SQL WILDCARDS Syntax

SELECT column_name(s) FROM table_name WHERE column_name LIKE "SOME Wildcards with CONIDTION"

Consider the following table

Firstname	Lastname	Age	Marital Status	Country
John	Smith	40	Married	Usa
Mathew	Simon	30	Married	Uk
Bill	Steve	20	Single	Usa
Amanda	Rogers	28	Married	Germany
Steve	Hills	30	Single	France
Steve	jobs	30	Single	Sweden
bill	cosby	30	Single	Switzerland

Example # 1

SELECT Firstname, Lastname FROM users WHERE Lastname LIKE "S_M%"

Result of the Query

Firstname	Lastname
Mathew	Simon

This result of the query includes all the data set whose last name Start with "S" then it skips one character as mentioned in the wildcard then the 3rd character is M as mentioned.

Example # 2

SELECT Firstname,Lastname,Country FROM users WHERE Country LIKE "SW%

Result of the Query

Firstname	Lastname	Country
Steve	jobs	Sweden
bill	cosby	Switzerland

This result of the query includes all the data set whose Country Name has "SW" in the start

Example # 3

SELECT Firstname,Lastname,Country FROM users WHERE Country LIKE "%an%"

Result of the Query

Firstname	Lastname	Country
Amanda	Rogers	Germany
Steve	Hills	France
bill	cosby	Switzerland

This result of the query includes all the data set whose Country name has "an" somewhere in the name.

Example # 4

SELECT Firstname, Lastname, Country FROM users WHERE Country LIKE "%an_"

Result of the Query

Firstname	Lastname	Country
Amanda	Rogers	Germany
bill	cosby	Switzerland

This result of the query includes all the data set whose Country name has "an" as the second last combination and whatsoever comes before it.

NOTICE: You can check the difference between the result of example 3 and example 4, they both have the same query but a bit different wild card. In example 3 the wild card accepts N number of character after the matching condition "an" and in example 4 it only accepts a single character after the matching condition "an".