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".

Add new comment