Skip to content
Tutorial Gateway
  • C
  • C#
  • Python
  • SQL
  • Java
  • JS
  • MySQL
  • BI Tools
    • Informatica
    • Talend
    • Tableau
    • Power BI
    • SSIS
    • SSRS
    • SSAS
    • MDX
    • R Tutorial
    • Alteryx
    • QlikView
  • More
    • C Programs
    • C++ Programs
    • Go Programs
    • Python Programs
    • Java Programs
    • Search
MySQL LIKE Escape 12

MySQL LIKE Operator

The MySQL LIKE Operator is used to perform a wild search on tables. The Like operator uses Wildcards to extract the records matching the specified pattern.

For instance, If you forgot the Product spelling or description, then use the MySQL LIKE operator Wildcard to find the matching records. The following are the supporting Wildcards.

WildcardsDescription
%Use this to match Zero or more characters.
_Use this to match one character exactly.

Before we get into the Wildcards in the MySQL LIKE Operator example, let me show you the syntax.

MySQL Like Operator Syntax

The syntax behind this Wildcard is:

SELECT Columns
FROM Table
WHERE Column_Name LIKE Wildcard_Expression ESCAPE 'escape_charcater'

The wildcard expression might contain % or _. If you want to escape any special character, then use default \ or any character. If you use other than \, then specify that character safer the ESCAPE keyword. For this MySQL LIKE operator demonstration, We are going to use the below-shown data.

Customer Table Rows 1

MySQL Like Operator Wildcard % Example

The Wildcard % (percentage) represents zero or more characters. For example, the below % wildcard query returns all the Employees whose First name starts with Letter R.

SELECT EmpID, 
       `First Name`,
       `Last Name`,
       Qualification,
       Occupation,
       Income,
       Sales,
       HireDate
 FROM customer
 WHERE `First Name` LIKE 'R%';
LIKE Wildcard% 2

This wildcard query example displays the Employees whose Occupation ends with l Letter.

SELECT EmpID, 
       `First Name`,
       `Last Name`,
       Qualification,
       Occupation,
       Income,
       Sales,
       HireDate
 FROM customer
 WHERE Occupation LIKE '%l';
MySQL LIKE Wildcard Operator 3

Multiple Values Example 2

The following Like multiple values query selects the Employees Whose Qualification starts with Letter B and ends with s.

SELECT EmpID, 
       `First Name`,
       `Last Name`,
       Qualification,
       Occupation,
       Income,
       Sales,
       HireDate
 FROM customer
 WHERE Qualification LIKE 'B%s';
Multiple Characters or Values in MySQL LIKE Wildcard Percentage 4

This query of Multiple values example returns the Customers Whose Qualification contains d and e in any position.

SELECT EmpID, 
       `First Name`,
       `Last Name`,
       Qualification,
       Occupation,
       Income,
       Sales,
       HireDate
FROM customer
WHERE Qualification LIKE '%d%e';
MySQL LIKE Operator 5

MySQL Like Wildcard _ Operator Example

The Wildcard Underscore sign (_) represents a single character. For instance, the following query selects the employees Whose Qualification includes the second letter as a.

SELECT EmpID, 
       `First Name`,
       `Last Name`,
       Qualification,
       Occupation,
       Income,
       Sales,
       HireDate
FROM customer
WHERE Qualification LIKE '_a%';
Like Wildcard _ Example 6

This query displays the Customers whose Qualification contains the second letter is a and t as a fourth letter.

SELECT EmpID, 
       `First Name`,
       `Last Name`,
       Qualification,
       Occupation,
       Income,
       Sales,
       HireDate
FROM customer
WHERE Qualification LIKE '_a_t%';
MySQL LIKE Operator 7

The below query displays the Employees whose Last name has at least six characters.

SELECT EmpID, 
       `First Name`,
       `Last Name`,
       Qualification,
       Occupation,
       Income,
       Sales,
       HireDate
FROM customer
WHERE  `Last Name` LIKE N'_%_%_%_%_%_%';
Using Like Wildcard Underscore and Percentage Example 8

Like Escape Example

Until now, we have seen wildcards to display one or more characters. However, if your column has some special characters such as _ or % or $, then you have to use the ESCAPE character. For this, We are going to use the below-shown data.

MySQL LIKE Escape 9

As you can see from the above screenshot, our data had _ and % symbols in the First Name column. In MySQL Like operator, \ is the default escape character to escape any special characters.

For example, the following query uses \ to escape _ from the first name.

SELECT EmpID, 
       `First Name`,
       `Last Name`,
       Qualification,
       Occupation,
       Income,
       Sales,
       HireDate
FROM customer
WHERE FirstName LIKE '%\_%';
MySQL LIKE Escape 10

You can also use your own character as the escape character by using the ESCAPE keyword. The below shown Like escape query uses # as the escape character to skip the _ symbol.

SELECT EmpID, 
       `First Name`,
       `Last Name`,
       Qualification,
       Occupation,
       Income,
       Sales,
       HireDate
FROM customer
WHERE FirstName LIKE '%#_%' ESCAPE '#';
Escape 11

This time we used $ as an escape character to escape @ symbol in the Email Address column.

SELECT EmpID, 
       `First Name`,
       `Last Name`,
       Qualification,
       Occupation,
       Income,
       Sales,
       HireDate
 FROM customer
 WHERE EmailAddress LIKE '%$@%' ESCAPE '$';
Escape 12

The following query escapes the % symbol in the First name.

SELECT CustomerKey,
       FirstName,
       LastName,
       BirthDate,
       EmailAddress,
       `Yearly Income`,
       Profession
 FROM customer12
 WHERE FirstName LIKE '%\%%';
Escape 13

Like wildcard Command Prompt Example

It is just an example to show you the Like operator in the command prompt.

Command Prompt Example 14

About Suresh

Suresh is the founder of TutorialGateway and a freelance software developer. He specialized in Designing and Developing Windows and Web applications. The experience he gained in Programming and BI integration, and reporting tools translates into this blog. You can find him on Facebook or Twitter.

Back to Categories MySQL
MySQL IFNULL Operator
MySQL NOT LIKE Operator

Related Topics

  • MySQL Tutorial
  • How to Download MySQL?
  • Install MySQL on Windows
  • MySQL Database & Table
    • Create Database
    • Delete Database
    • Create Table
    • Drop Table
  • MySQL Basic Commands
    • INSERT Statement
    • DELETE Statement
    • SELECT Statement
    • SELECT DISTINCT Statement
    • SELECT ALIAS Column
    • SELECT LIMIT
    • TRUNCATE TABLE
  • MySQL Clauses
    • MySQL GROUP BY Clause
    • MySQL Having Clause
    • MySQL ORDER BY Clause
    • MySQL WHERE Clause
  • MySQL Operators
    • Arithmetic Operators
    • AND Operator
    • OR Operator
    • NOT Operator
    • XOR Operator
    • BETWEEN Operator
    • Not Between Operator
    • LIKE Operator
    • NOT LIKE Operator
    • IS Operator
    • IN Operator
    • NOT IN Operator
    • INTERVAL Operator
  • MySQL Working on NULLS
    • MySQL COALESCE
    • MySQL IFNULL
    • MySQL NULLIF
    • MySQL IS NULL
    • MySQL IS NOT NULL
  • MySQL Joins
    • MySQL Cross Join
    • MySQL Inner Join
    • MySQL Left Join
    • MySQL Right Join
  • MySQL Comparison Func
    • GREATEST Function
    • LEAST Function
  • MySQL Agg Functions
    • Introduction
    • AVG Function
    • BIT_AND Function
    • BIT_OR Function
    • BIT_XOR Function
    • MAX Function
    • MIN Function
    • SUM Function
    • STDDEV_POP Function
    • STDDEV_SAMP Function
    • VAR_POP Function
    • VAR_SAMP Function
  • MySQL Date Functions
    • Introduction to Date Functions
    • ADDDATE Function
    • ADDTIME Function
    • CONVERT_TZ Function
    • CURDATE Function
    • Current Date
    • CURTIME Function
    • Current Time
    • CURRENT_TIMESTAMP
    • Date Function
    • DATE_FORMAT
    • DATE_SUB Function
    • DATE_ADD Function
    • DATEDIFF Function
    • Day Function
    • DAYNAME Function
    • DAYOFYEAR Function
    • DAYOFWEEK
    • DAYOFMONTH
    • EXTRACT Function
    • FROM_UNIXTIME
    • FROM_DAYS Function
    • GET_FORMAT Function
    • LAST_DAY Function
    • LOCALTIME Function
    • LOCALTIMESTAMP
    • MAKEDATE Function
    • MAKETIME Function
    • MICROSECOND Function
    • MINUTE Function
    • MONTHNAME Function
    • MONTH Function
    • NOW Function
    • PERIOD_DIFF Function
    • PERIOD_ADD Function
    • QUARTER Function
    • SECOND Function
    • SEC_TO_TIME Function
    • STR_TO_DATE Function
    • SUBDATE Function
    • SUBTIME Function
    • SYSDATE Function
    • TIME Function
    • TIME_FORMAT Function
    • TIMEDIFF Function
    • TIMESTAMPDIFF Function
    • TIMESTAMPADD Function
    • TIMESTAMP Function
    • TIME_TO_SEC Function
    • TO_SECONDS Function
    • TO_DAYS Function
    • UNIX_TIMESTAMP Function
    • UTC_TIMESTAMP Function
    • UTC_TIME Function
    • UTC_DATE Function
    • WEEK Function
    • WEEKDAY Function
    • WEEKOFYEAR Function
    • YEARWEEK Function
    • Year Function
  • MySQL String Functions
    • Introduction to Strings
    • ASCII Function
    • BIN Function
    • BIT_LENGTH Function
    • CHAR_LENGTH Function
    • CHAR Function
    • CONCAT_WS Function
    • CONCAT Function
    • ELT Function
    • EXPORT_SET Function
    • FIELD Function
    • FIND_IN_SET Function
    • FORMAT Function
    • HEX Function
    • UNHEX Function
    • INSERT Function
    • INSRT Function
    • LCASE Function
    • LEFT Function
    • LENGTH Function
    • LOCATE Function
    • LOWER Function
    • LPAD Function
    • LTRIM Function
    • MAKE_SET Function
    • MID Function
    • OCTET_LENGTH Function
    • OCT Function
    • ORD Function
    • POSITION Function
    • QUOTE Function
    • REPLACE Function
    • REPEAT Function
    • REVERSE Function
    • RIGHT Function
    • RTRIM Function
    • RPAD Function
    • SOUNDS LIKE Function
    • SOUNDEX Function
    • STRCMP Function
    • SPACE Function
    • SUBSTRING Function
    • SUBSTRING_INDEX Function
    • SUBSTR Function
    • TRIM Function
    • UCASE Function
    • UNHEX Function
    • UPPER Function
  • MySQL Math Functions
    • Introduction to math
    • ABS Function
    • CEILING Function
    • CONV Function
    • CRC32 Function
    • DEGREES Function
    • EXP Function
    • FLOOR Function
    • LN Function
    • LOG Function
    • LOG2 Function
    • LOG10 Function
    • PI Function
    • POW Function
    • POWER Function
    • RAND Function
    • RADIANS Function
    • ROUND Function
    • SIGN Function
    • SQRT Function
    • TRUNCATE Function
  • MySQL Trigono Functions
    • SIN Function
    • ASIN Function
    • COS Function
    • ACOS Function
    • TAN Function
    • ATAN Function
    • ATAN2 Function
  Copyright © 2023. All Rights Reserved.
Home | About | Contact | Privacy Policy