EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 360+ Courses All in One Bundle
  • Login
Home Data Science Data Science Tutorials Database Management Tutorial Wildcard Characters
Secondary Sidebar
Database Management Tutorial
  • DataBase Management
    • Text Data Mining
    • Roles of Database Management System in Industry
    • SQL Server Database Management Tools
    • Databricks CLI
    • Database administrator skills
    • Database Management Systems Advantages
    • Database Testing Interview Questions
    • Netezza Database
    • Data Administrator
    • Database Administrator
    • Data manipulation
    • Database Management Software
    • DataStage
    • Types of Database Models
    • Types of Database
    • Hierarchical Database Model
    • Relational Database
    • Relational Database Advantages
    • Operational Database
    • What is RDBMS?
    • Data Masking Tools
    • Database Security
    • Data Replication
    • Bitmap Indexing
    • Second Normal Form
    • Third Normal Form
    • Fourth Normal Form
    • Data Definition Language
    • Data Manipulation Language
    • Data Control Language
    • Transaction Control Language
    • Dataset Normalization
    • jdbc connection
    • Conceptual Data Model
    • Entity-Relationship Model
    • Relational Database Model
    • Sequential File Organization
    • Teradata Create Table
    • Teradata Database
    • Centralized Database
    • Data Storage in Database
    • Thomas write Rule
    • DBA Interview Questions
    • What is JDBC?
    • jdbc hive
    • Apriori Algorithm
    • JDBC Architecture
    • JDBC Interview Questions
    • Datastage Interview Questions
    • Wildcard Characters
    • Distributed Database System
    • Multidimensional Database
  • TSQL Basic
    • TSQL
    • What is T-SQL
    • T-SQL Commands
    • T-SQL String Functions
    • TSQL Interview Questions

Related Courses

SQL Certification Course

PL/SQL Certification Course

Oracle Certification Course

Wildcard Characters

By Priya PedamkarPriya Pedamkar

Wildcard-Characters

Introduction to Wildcard Characters

A Wildcard Character is a character or a group of characters that can be searched and replaced in a particularly given string. Wildcard characters are used with MySQL queries with the LIKE operator. This LIKE operator is used in the where clause of the MySQL query. We can search for a particular pattern for a particular column of the table with the where clause.

Example:

% wildcard is used to like %ab will find all ab in a given set of data(records) producing the output like about, above, abundance, absorbent and so on.

Why do we Use Wildcards?

As you are familiar with MySQL, we have various queries like INSERT, UPDATE, SELECT, DELETE, etc. Since wildcards do the job of searching a particular pattern, the SELECT statement along with the WHERE clause will come into the picture rather than other MySQL queries. To make the search with wildcards easier, let us look into a simple example of artists and his paintings example. Say, there are different paintings created by different artists available in different regions of the world at different prices. Let the database name be of your choice say “search_artist.”

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

The table name is the artist.

Here is the create a query to create the table ‘artist.’

Code:

CREATE TABLE `artist` (
`artist_id` int(11) NOT NULL,
`artist_name` varchar(255) NOT NULL,
`artist_painting` varchar(255) NOT NULL,
`artist_country_name` varchar(255) NOT NULL,
`artist_country_code` varchar(255) NOT NULL,
`artist_painting_price` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

After the table is created we will insert the data in the table.

Dumping data for table `artist.`

Code:

INSERT INTO `artist` (`artist_id`, `artist_name`, `artist_painting`, `artist_country_name`, `artist_country_code`, `artist_painting_price`) VALUES
(1, 'Leonardo Da Vinci', 'Mona Lisa', 'Angola', 'AO', '$500'),
(2, 'Edvard Munch', 'The Scream', 'Bahrain', 'BH', '$600'),
(3, 'Sistine Chapel by Michelangelo', 'The Creation of Adam', 'Bangladesh', 'BD', '$700'),
(4, 'Vincent Van Gogh', 'Sun Flowers', 'Malaysia', 'MY', '$550'),
(5, 'Rene Magritte', 'Ceci N’est pas une Pipe', 'Madagascar', 'MG', '$850'),
(6, 'Edvard Munch', 'The Scream', 'Portugal', 'PT', '$750'),
(7, 'Leonardo Da Vinci', 'Mona Lisa', 'Poland', 'PL', '$500'),
(8, 'Claude Monet', 'Poppies in a Field', 'Vietnam', 'VN', '$650');
 

Once you execute the above query, the following table will be created.

Table1

1. The % percentage

Example One: Now we want to know which all artist who has their painting in the countries where the country name starts with “BA.”

The Search query goes like this:

All in One Data Science Bundle(360+ Courses, 50+ projects)
Python TutorialMachine LearningAWSArtificial Intelligence
TableauR ProgrammingPowerBIDeep Learning
Price
View Courses
360+ Online Courses | 50+ projects | 1500+ Hours | Verifiable Certificates | Lifetime Access
4.7 (86,408 ratings)

Code:

SELECT * FROM artist WHERE artist_country_name LIKE 'Ba%'
The result is country names starting with Ba are “Bahrain” and “Bangladesh”

Executing the above query in MySQL:

Table2

Output of the query:

Table3

Example Two: Here, we want to know which all artist has their painting in the countries containing “al.”

The solution is:

Code:

SELECT * FROM artist WHERE artist_country_name LIKE '%al%'
The result is country names starting with Ba are “Malaysia” and “Portugal”

Executing the above query in MySQL:

Table4

Output of the query:

Table6

In the above two examples, we have a single wildcard, which is ‘%.’

Now we will check for related wildcards like the _ wildcard (the underscore wildcard), the escape keyword, NOT LIKE keyword, etc.

2. _ underscore Wildcard

Example One: This is also used with the SELECT statement along with where clause where in the _ stands for any single character, which could be anything.

The solution is:

Code:

SELECT * FROM artist WHERE `artist_painting_price` LIKE '$5_0'
The result is painting price names with this pattern will give output as
$500, $550, $500 in the painting price column

Executing the above query in MySQL:

Table7

Output of the query:

Table8

Example Two:

The solution is:

Code:

SELECT * FROM artist WHERE `artist_country_code` LIKE 'A_’

Executing the above query in MySQL:

Table9

Output of the above query:

Table10

Before diving into the third wildcard, we will see the combination of the above two wildcards, the combination of ‘_’ and ‘%’ wildcards.

Wildcard Combination Meaning
WHERE artist_painiting LIKE ‘Mo%.’ Returns all the values that start with “Mo.”
WHERE artist_country_name LIKE ‘%n.’ Returns all the values that end with “n.”
WHERE artist_country_name LIKE ‘%da%’ Returns all the values that have “da” in any position.
WHERE artist_country_name LIKE ‘_o%’ Returns all the values that have “o” in the second position.
WHERE artist_name LIKE ‘L%i.’ Returns all the values that start with “L” and ends with “i.”

3. NOT LIKE Wildcard

This NOT LIKE operator, like others, are used with the where clause of the select statement and the underscore and percentage wildcard. It returns the rows from the table where the given pattern does not match.

Example One: We do not want the records where the artist painting has the price of $600 and $650.

The solution is:

Code:

SELECT * FROM artist WHERE `artist_painting_price` NOT LIKE '$5%'

Executing the above query in MySQL with Output:
Table11

4. [^characterlist] with REGEXP

Example One: We want the records containing all characters that match the pattern present within the bracket.

The solution is:

Code:

SELECT * FROM artist WHERE `artist_country_name` REGEXP '^[po]'

Here, the artist_country_name containing the group of characters like po results into two records which contains Portugal and Poland only.

Executing the above query in MySQL with Output:

Wildcard Characters - Table12

5. ^ NOT with REGEXP keyword

Example: We want the records containing all characters except those present within the bracket.

The solution is:

Code:

SELECT * FROM artist WHERE artist_country_name REGEXP '^[^po]'

Executing the above query in MySQL with output:

Wildcard Characters - Table13

6. Escape Keyword

Example One: In this example, the artist’s name is the input that is searched in a table for a given pattern using an escape keyword. It will be more clear with the following example.

The solution is:

Code:

SELECT * FROM artist WHERE `artist_name` LIKE '%$ Da %' ESCAPE '$'

Executing the above query in MySQL with Output.

Wildcard Characters - Table14

7. ‘|’ keyword to perform a search based on given alphabets

Example: We want the records whose first names start with character L or S.

The solution is:

Code:

SELECT * FROM artist WHERE `artist_painting` REGEXP '^(L|S)'

Executing the above query in MySQL with Output:

Wildcard Characters - Table15

Conclusion – Wildcard Characters

With this topic you saw % and _ wildcards with examples. Also, you saw NOT to LIKE Operator, the search query with the escape keyword, the [^characterlist] and others. All this was made clear by performing a SELECT query with a where clause on the “artist” table. Hopefully, each topic got easier to understand and grasp.

Recommended Articles

This has been a guide to Wildcard Characters. Here we have discussed what wildcard characters are, how to use wildcard and how to create a table etc. You can also go through our other suggested articles to learn more –

  1. Wildcard in SQL
  2. Filters in Tableau
  3. Uses of SQL
  4. T-SQL String Functions
Popular Course in this category
SQL Training Program (7 Courses, 8+ Projects)
  7 Online Courses |  8 Hands-on Projects |  73+ Hours |  Verifiable Certificate of Completion
4.5
Price

View Course

Related Courses

PL SQL Training (4 Courses, 2+ Projects)4.9
Oracle Training (14 Courses, 8+ Projects)4.8
0 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

ISO 10004:2018 & ISO 9001:2015 Certified

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

EDUCBA
Free Data Science Course

SPSS, Data visualization with Python, Matplotlib Library, Seaborn Package

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

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

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

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

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

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more