MySQL FAQs
FAQs Categories
Client Server Commands
Database Structure
Table Types or Storage Engines
Indexes
SQL Statements
Table Joins
Funtions and Operators
Tricky Select Queries
Speed Up Queries
Data Back Up
General Questions
Errors
N.M.P.L. Company www.NearMePayday.Loan || 3-month (90 days)
Powered by MySQL
 
Home / SQL Statements / Question No: 223

How to use LIKE pattern-matching operator?

Answer No: 223

Operators such as = and != are useful for finding values that are equal to or not equal to a specific exact comparison value. When it's necessary to find values based on similarity instead, a pattern match is useful. To perform a pattern match, use value LIKE 'pattern', where value is the value you want to test and 'pattern' is a pattern string that describes the general form of values that you want to match.

Patterns used with the LIKE pattern-matching operator can contain two special characters (called "metacharacters" or "wildcards") that stand for something other than themselves:

  • The '%' character matches any sequence of zero or more characters. For example, the pattern 'a%' matches any string that begins with 'a', '%b' matches any string that ends with 'b', and '%c%' matches any string that contains a 'c'. The pattern '%' matches any string, including empty strings.
  • The '_' (underscore) character matches any single character. 'd_g' matches strings such as 'dig', 'dog', and 'd@g'. Because '_' matches any single character, it matches itself and the pattern 'd_g' also matches the string 'd_g'.

A pattern can use these metacharacters in combination. For example, '_%' matches any string containing at least one character.

LIKE evaluates to NULL if either operand is NULL, but any non-NULL literal value matches itself. Likewise, a function call that produces a non-NULL value matches itself (with one exception). Thus, the following expressions evaluate as true:

'ABC' LIKE 'ABC'
column_name LIKE column_name
VERSION() LIKE VERSION()

The exception is that different invocations of the RAND() random-number function might return different values, even within the same query:

mysql> SELECT RAND(), RAND();
+------------------+------------------+
| RAND()           | RAND()           |
+------------------+------------------+
| 0.15430032289987 | 0.30666533979277 |
+------------------+------------------+

As a result, the expression RAND() LIKE RAND() normally will be false.

LIKE performs a non-binary comparison if both operands are non-binary strings; otherwise, the comparison is binary:

mysql> SELECT 'ABC' LIKE 'abc', 'ABC' LIKE BINARY 'abc';
+------------------+-------------------------+
| 'ABC' LIKE 'abc' | 'ABC' LIKE BINARY 'abc' |
+------------------+-------------------------+
|                1 |                       0 |
+------------------+-------------------------+

To invert a pattern match, use NOT LIKE rather than LIKE:

mysql> SELECT 'ABC' LIKE 'A%', 'ABC' NOT LIKE 'A%';
+-----------------+---------------------+
| 'ABC' LIKE 'A%' | 'ABC' NOT LIKE 'A%' |
+-----------------+---------------------+
|               1 |                   0 |
+-----------------+---------------------+

MySQL, unlike some other database systems, allows use of LIKE with non-string values. This can be useful in some cases. For example, the expression d LIKE '19%' is true for date values d that occur during the 1900s. MySQL evaluates such comparisons by converting non-string values to strings before performing the pattern match.

It's possible to specify the pattern in a LIKE expression using a table column. In this case, the actual pattern that a value is compared to can vary for every row of a result set. The following table has one column containing patterns and another column that characterizes the type of string each pattern matches:

mysql> SELECT pattern, description FROM patlist;
+---------+--------------------------------+
| pattern | description                    |
+---------+--------------------------------+
|         | empty string                   |
| _%      | non-empty string               |
| _ _ _   | string of exactly 3 characters |
+---------+--------------------------------+

The patterns in the table can be applied to specific values to characterize them:

mysql> SELECT description, IF('' LIKE pattern,'YES','NO') FROM patlist;
+--------------------------------+--------------------------------+
| description                    | IF('' LIKE pattern,'YES','NO') |
+--------------------------------+--------------------------------+
| empty string                   | YES                            |
| non-empty string               | NO                             |
| string of exactly 3 characters | NO                             |
+--------------------------------+--------------------------------+

mysql> SELECT description, IF('abc' LIKE pattern,'YES','NO') FROM patlist;
+--------------------------------+-----------------------------------+
| description                    | IF('abc' LIKE pattern,'YES','NO') |
+--------------------------------+-----------------------------------+
| empty string                   | NO                                |
| non-empty string               | YES                               |
| string of exactly 3 characters | YES                               |
+--------------------------------+-----------------------------------+

mysql> SELECT description, IF('hello' LIKE pattern,'YES','NO') FROM patlist;
+--------------------------------+-------------------------------------+
| description                    | IF('hello' LIKE pattern,'YES','NO') |
+--------------------------------+-------------------------------------+
| empty string                   | NO                                  |
| non-empty string               | YES                                 |
| string of exactly 3 characters | NO                                  |
+--------------------------------+-------------------------------------+

To match a pattern metacharacter literally, escape it by preceding it by a backslash:

mysql> SELECT 'AA' LIKE 'A%', 'AA' LIKE 'A%', 'A%' LIKE 'A%';
+----------------+-----------------+-----------------+
| 'AA' LIKE 'A%' | 'AA' LIKE 'A%' | 'A%' LIKE 'A%' |
+----------------+-----------------+-----------------+
|              1 |               0 |               1 |
+----------------+-----------------+-----------------+

mysql> SELECT 'AA' LIKE 'A_', 'AA' LIKE 'A_', 'A_' LIKE 'A_';
+----------------+-----------------+-----------------+
| 'AA' LIKE 'A_' | 'AA' LIKE 'A_' | 'A_' LIKE 'A_' |
+----------------+-----------------+-----------------+
|              1 |               0 |               1 |
+----------------+-----------------+-----------------+

To specify a given character as the escape character, use an ESCAPE clause:

mysql> SELECT 'AA' LIKE 'A@%' ESCAPE '@', 'A%' LIKE 'A@%' ESCAPE '@';
+----------------------------+----------------------------+
| 'AA' LIKE 'A@%' ESCAPE '@' | 'A%' LIKE 'A@%' ESCAPE '@' |
+----------------------------+----------------------------+
|                          0 |                          1 |
+----------------------------+----------------------------+

Related MySQL FAQs to the Above FAQ

What-does-alias-means-in-MySQL What does alias means in MySQL?

What-does-SQL-Expressions-mean-in-MySQL What does SQL Expressions mean in MySQL?

How-to-use-numeric-expressions-in-MySQL How to use numeric expressions in MySQL?

How-to-use-string-expressions-in-MySQL How to use string expressions in MySQL?

How-to-manage-case-sensitivity-in-string-comparisons How to manage case sensitivity in string comparisons?

How-to-use-date-or-temporal-expressions How to use date or temporal expressions?

What-does-NULL-value-mean-in-MySQL What does NULL value mean in MySQL?

How-to-use-comments-in-SQL-statements How to use comments in SQL statements?

How-to-validate-user-login-in-case-sensitive-manner How to validate user login in case sensitive manner?

What-is-difference-between-delete-and-truncate-statements-of-MySQL What is difference between delete and truncate statements of MySQL?

About FAQs: Recently Added FAQs

About MySQL FAQs: Site Map | Bookmark Us | Recommend this Site to Your Friend | Contact Us

Useful Links: Wikipedia.org | Oracle.com | w3schools.com | www.php.net | Github.com

© 2021  www.mysqlfaqs.net
All rights reserved.