TestingDocs.com
Software Testing website
  • Automation
    • Selenium
    • JBehave Framework
  • Tutorials
    • MySQL Tutorials
    • Testlink
    • Maven
    • Git
  • IDEs
    • IntelliJ IDEA
    • Eclipse
  • Flowcharts
    • Flowgorithm
    • Raptor
  • About

MySQL

MySQL LIKE Operator

Overview

In this tutorial, we will learn about MySQL LIKE operator. We can use the LIKE operator in the query to check if column values match a specified pattern and search for similar values. We can use this operator when we are not sure of the exact search condition.

Syntax

The general syntax of the LIKE operator is as follows:

SELECT <column_list>
FROM <table_name>
WHERE column LIKE pattern;

The LIKE operator supports two wildcards:

Wildcard Description
Underscore ( _ ) This wildcard represents exactly one value in the pattern.
Percentage ( % )

 

This wildcard represents zero or more values in the pattern.

 

Example

Underscore wildcard

In this example, we will query the Country table from the world MySQL database.

We cannot use the = operator in the WHERE clause to match the partial matches.

mysql> SELECT Count(*) FROM Country
-> WHERE Continent =’___th America’;

+———-+
| Count(*) |
+———-+
| 0 |
+———-+

We have use the LIKE operator with the wildcards. The underscore (_) matches exactly one character. The query
uses three underscores to match any three characters.

mysql> SELECT Count(*) FROM Country
-> WHERE Continent LIKE ‘___th America’;

+———-+
| Count(*) |
+———-+
| 51 |
+———-+

The query matches the continents like ‘North America’ and ‘South America’.

 

MySQL Like Underscore Wildcard

Percentage wildcard

In this example, we will query the City table from the world MySQL database.We will use the LIKE operator to retrieve rows from the table with the District names that start with ‘New’. We will use the wildcard (%) to match any characters after the string ‘New’ in the district name.

 

mysql> SELECT * FROM City
-> WHERE District LIKE ‘New %’;

+——+—————+————-+—————–+————+
| ID | Name | CountryCode | District | Population |
+——+—————+————-+—————–+————+
| 130 | Sydney | AUS | New South Wales | 3276207 |
| 137 | Newcastle | AUS | New South Wales | 270324 |
| 138 | Central Coast | AUS | New South Wales | 227657 |
| 139 | Wollongong | AUS | New South Wales | 219761 |
| 148 | Nassau | BHS | New Providence | 172000 |
| 3793 | New York | USA | New York | 8009000 |
| 3827 | Albuquerque | USA | New Mexico | 448607 |
| 3850 | Buffalo | USA | New York | 292648 |
| 3855 | Newark | USA | New Jersey | 273546 |
| 3864 | Jersey City | USA | New Jersey | 240055 |
| 3871 | Rochester | USA | New York | 219773 |
| 3888 | Yonkers | USA | New York | 196086 |
| 3932 | Paterson | USA | New Jersey | 149222 |
| 3935 | Syracuse | USA | New York | 147306 |
| 3978 | Elizabeth | USA | New Jersey | 120568 |
| 4011 | Manchester | USA | New Hampshire | 107006 |
| 4048 | Albany | USA | New York | 93994 |
+——+—————+————-+—————–+————+
17 rows in set (0.01 sec)

 

MySQL LIKE Operator Example

—

MySQL Tutorials

MySQL Tutorials on this website:

https://www.testingdocs.com/mysql-tutorials-for-beginners/

For more information on MySQL Database:

https://www.mysql.com/

Related Posts

DataGrip Desktop Shortcut

MySQL /

Launch DataGrip on Windows

DataGrip Download

MySQL /

Install DataGrip IDE on Windows 11

MySQL Workbench Windows 11

MySQL /

New MySQL Connection in Workbench

MySQL Command-line Client

MySQL /

Command-Line MySQL Client

Start MySQL Client

MySQL /

Start MySQL Client on Windows 11

‹ MySQL CASE Function› MySQL IF Function

Recent Posts

  • Update draw.io on Windows
  • Install RAPTOR Avalonia on CentOS
  • Download RAPTOR Avalonia Edition on Windows
  • npm doctor command
  • Build & Run CLion Project
  • Create New CLion C Project on Windows
  • Configure CLion Toolchains on Windows
  • Launch CLion IDE on Windows
  • Activate CLion IDE
  • CLion IDE for C/C++ Development

Back to Top

Links

  • Contact
  • Privacy Policy
  • Cookie Policy

www.TestingDocs.com