EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Data Science Data Science Tutorials SQL Tutorial Wildcard in SQL
 

Wildcard in SQL

Priya Pedamkar
Article byPriya Pedamkar

Updated March 20, 2023

Wildcard in SQL

 

 

Overview of Wildcard in SQL

A wildcard character in SQL replaces in a string zero to any number of characters. Usually, these wildcard characters can be found being used with the SQL operator LIKE. This is an operator that is commonly utilized in the WHERE clause of SQL to hunt for a particular arrangement of characters.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

Wildcards have a similar goal as that of Regular Expressions. A wildcard character is a substitute character that is used to replace specific other characters or characters in that string. These wildcards are beneficial when we want to accomplish a quick search for in the database.

We have two common wildcard characters in SQL. The percent symbol signifies zero, one or any number of characters. The underscore symbol denotes a single character, either a letter or a number. These signs can be combined in several arrangements. We also have [charlist], [^charlist], and [!charlist], which can be employed in both SQL and MS Access.

We will look at these wildcard characters in detail, along with a few other useful wildcard characters.

Wildcard Description Example
Percent sign: % This wildcard matches zero or more characters ha% finds ha, hat, happy, and happen
Underscore: _ This wildcard matches only a specific single character p_t find pot, pit, and put
Square brackets: [] This wildcard matches a solo character specified within the brackets p[oi]t find pot and pit, but not put
Caret: ^ This wildcard matches characters which are not present in the bracket after this symbol p[^oi]t finds put, but not pot and pit

Let us note that MS Access uses the character asterisk symbol (*) for the wildcard in place of the percent symbol (%) wildcard character to match zero or more characters, and it uses the character question mark (?) for the wildcard in place of the underscore wildcard character to represent a single character.

Syntax of SQL Wildcards

Let us look at the number of ways the Wildcard character ‘%’ and ‘_’ can be written:

SELECT FROM table_name
WHERE column LIKE 'AAAA%'

Or

SELECT FROM table_name
WHERE column LIKE '%AAAA%'

Or

SELECT FROM table_name
WHERE column LIKE '_AAAA'

Or

SELECT FROM table_name
WHERE column LIKE 'AAAA_'

Or

SELECT FROM table_name
WHERE column LIKE '_ AAAA _'

AAAA% helps us look for any strings beginning with AAAA and ending with another single character or more than a single character.

%AAAA% helps us to look for any strings beginning with any number of characters but containing the string AAAA in between and ending with any number of characters from 0 to infinity.

_AAAA helps us to look for any strings beginning with a single distinct character and ending with the string AAAA.

AAAA_ helps us to look for any strings beginning with AAAA pattern and ending with a single distinct character.

_AAAA_ helps us to look for any strings beginning with a distinct character, containing the pattern AAAA in between and ending with a single distinct character.

Examples for Each Wildcard Character in SQL

Here are some of the Wildcard Character examples given below

Wildcard Character

1) Working with the % Wildcard

With this example, we can select all the employees of a city starting with “the”:

SELECT * FROM Employees
WHERE City LIKE 'the%';

2) Working with the _ Wildcard

With this example, we can select all the employees of a city starting with a single distinct character but ending with “elhi”:

SELECT * FROM Employees
WHERE City LIKE '_elhi';

Let us look at another example where we can select Employees of the city whose name starts with “B”, followed by a single distinct character, followed by “r”, followed by a single distinct character, followed by “in”:

SELECT * FROM Employees
WHERE City LIKE 'B_r_in';

3) Combining % and _ Wildcard

We can combine both the wildcard characters to optimize our searching process.

With this example, we can find any string having ‘a’ in the second position.

WHERE StringName LIKE '_a%'

With this example, we can find any string beginning with ‘x’ and having a length of a minimum of three characters.

WHERE StringName LIKE 'x_%_%'

With this example, we can find any string beginning with ‘x’ and ending with ‘y’, with a minimum of one character in between.

WHERE StringName LIKE 'x%_y'

4) Working with the [] Wildcard

With this example, we can select all the employees of a city whose name begins with either “a”, “b”, or “c”.

SELECT * FROM Employees
WHERE City LIKE '[abc]%';

We can write this same example in another way by using the hyphen (-) symbol. This statement will also select all the employees of a city whose name begins with either “a”, “b”, or “c”. It can be written as follows:

>SELECT * FROM Employees
WHERE City LIKE '[a-c]%';

5) Working with the [! ] Wildcard

With this example, we can select all the employees of a city whose name does NOT begin with either “a”, “b”, or “c”.

SELECT * FROM Employees
WHERE City LIKE '[!abc]%';

This statement can also be written as follows:

SELECT * FROM Employees
WHERE City NOT LIKE '[abc]%';

Conclusion

In this SQL Wildcard article, we have looked at all the wildcards in SQL. Furthermore, we looked at various SQL Wildcard Characters’ examples to give us more understanding and clarity.

To conclude, Wildcard characters do similar work as that of Regular Expressions. We can combine multiple wildcards together in a solo string to obtain better search outcomes and results. A few databases like MS Access could use a separate SQL wildcard for a similar function.

Recommended Articles

We hope that this EDUCBA information on “Wildcard in SQL” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

  1. SQL Views
  2. SQL Management Tools
  3. What is MySQL?
  4. Introduction to MySQL

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA Login

Forgot Password?

🚀 Limited Time Offer! - 🎁 ENROLL NOW