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 MySQL Tutorial Wildcards in MySQL
 

Wildcards in MySQL

Priya Pedamkar
Article byPriya Pedamkar

Updated May 10, 2023

Wildcards in MySQL

 

 

Introduction to Wildcards in MySQL

In SQL, a wildcard is an operator that replaces zero to any number of characters in a string. To identify a particular arrangement of characters from all required values of a text field, you can use a specific symbol to denote the wildcard operator, and use it with the LIKE operator in the WHERE clause. There are two forms of wildcards, namely percentage and underscore.

Watch our Demo Courses and Videos

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

Features​ ​​of Wildcards

Here are some important features of Wildcards in SQL:

  1. Using wildcards in MySQL can increase the performance of an application.
  2. It can reduce the time to filter the record from the database.
  3. Wildcards can simplify complex SQL queries.
  4. We can develop powerful search engines in a large data-driven application using wildcards. Searching in the data-driven application is much more dependent on the use of wildcards.

Type of Wildcards​

Following are some types of wildcards that can be used in SQL queries. You can use wildcards individually or in combination with other wildcards in SQL queries.

Type of Wildcards​

1) % The Percentage Character

​% Symbol characters can be used either in searching or filtering the record. % can be used either in the first place, in the last, or on both sides of the string, Such as

SELECT * FROM 'items' WHERE 'item_description' LIKE '%Motor%';

The above query extracts all rows from the database where the column ‘item_description’ contains the word ‘Motor’ in the middle of the description text.

SELECT * FROM 'items' WHERE 'item_description' LIKE '%Motor';

The above query extracts all rows from the database where the column ‘item_description’ contains the word ‘Motor’ at the end of the description text.

SELECT * FROM 'items' WHERE 'item_description' LIKE 'Motor%';

The above query extracts all rows from the database where the column ‘item_description’ contains the word ‘Motor’ at the beginning of the description text.

Example – ​A ‘items’ table containing the following record in the below-given table

Percentage Character - Wildcards SQL

We need all items in the given table that contain the name ‘motor’. So the SQL query will use the wildcard character “%” in searching for the needed data from the database.

SELECT * FROM 'items' WHERE 'item_description' LIKE %Motor%;

Percentage Character

After execution of the SQL query, above-given record will be available.

2) _ The Underscore Character

In SQL queries, the underscore (_) wildcard can be used to filter records based on a single character at any location in a string. This wildcard character can be helpful when any character may appear at that position.

Example: ​In the below given `items` table, there are four items available When needing to filter the record, which having first four characters are `AH00` & the last three characters are `2EC` & only the 5th position character may be anything, in this type of scenario wildcard character _ will be needed at the 5th position in the SQL query to filter the record from the database.

Underscore Character

Below is the query to find the expected record from the database.

SELECT * FROM 'items' WHERE 'item_code' LIKE 'AH00_2EC';

Underscore Character

In the below-given query, using _ (underscore) in the combination of 2. Here

SELECT * FROM 'items' WHERE 'item_code' LIKE 'AH00__EC';

wildcard (underscore)

Depending on the requirement, this wildcard _ (underscore) can be used multiple times in the SQL query at any position. Also, it can be used in combination with other wildcard characters.

3) – The Hyphen Character Wildcard

In SQL queries, you can use the hyphen (-) character as a wildcard to filter records containing a character within a certain range at any position. This can be a useful technique for efficiently searching and filtering the database.

Example – ​In the above given `items` table, there is a need for all those items whose names start from a to j. In this case, queries will be as given below

SELECT * FROM 'items' WHERE 'item_description' LIKE '[a-j]%';

4) [] The Square Bracket Wildcard

​([]) Square bracket characters can be used in the query to select all data that can have a string, as in C & I at the particular location;

Example – ​In the above given `items` table, there is a need for all those items whose name matches with starting characters C & Ij. In this case, queries will be as given below

SELECT * FROM 'items' WHERE 'item_description' LIKE '[CI]%';

5) ^ The Caret Wildcard 

​^ The Caret character can be used in the query to de-select all those records that start with characters C & I.

SELECT * FROM 'items' WHERE 'item_description' LIKE '[^CI]%';

6) # Hash Wildcard

You can use the Hash character (#) in a query to select all records that contain any numeric character in place of the # wildcard.

Conclusion​ – Wildcards in MySQL

Wildcards used in all types of databases like MySQL, MS Access, Oracle. Wildcard work the same as regular expressions works. In the SQL query, you can use multiple wildcards simultaneously to search and filter the database. You can use each wildcard individually or combine them with other wildcards.

Recommended Articles

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

  1. MySQL Server
  2. String functions in MySQL
  3. MySQL Operators
  4. MySQL BETWEEN

 

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