MySQL LIKE

Last Updated: 02 July, 2023

In MySQL, the LIKE keyword is used in conjunction with the SELECT statement to perform pattern matching on string columns. It allows us to search for records that contain a specific pattern or substring within a column's value.

The basic syntax of the LIKE operator is as follows:

 SELECT column1, column2, ...
 FROM table_name
 WHERE column_name LIKE pattern;

Here, column_name is the name of the column we want to search, and pattern is the string pattern we're looking for. The pattern can include wildcard characters to represent any sequence of characters.

The two most commonly used wildcard characters in LIKE are:

1. % (percent sign): Represents any sequence of characters, including none.
2. _ (underscore): Represents a single character.

Here are some examples to illustrate the usage of the LIKE operator:

To find all records where the "name" column starts with "John":

 SELECT * FROM users WHERE name LIKE 'John%';

To find all records where the "email" column contains the domain "@gmail.com":

 SELECT * FROM users WHERE email LIKE '%@gmail.com';

To find all records where the "phone" column ends with "123":

 SELECT * FROM users WHERE phone LIKE '%123';

To find all records where column1 contains "123" anywhere in the value:

  SELECT column1, column2 FROM table WHERE column1 LIKE '%123%';

To find all records where the "address" column contains a single character followed by "oom":

 SELECT * FROM users WHERE address LIKE '_oom';

Find all products whose names have exactly five characters:

 SELECT * FROM products
 WHERE product_name LIKE '_ _ _ _ _';

These examples demonstrate basic usage of the LIKE operator. We can combine multiple wildcard characters and use them in different positions within the pattern to perform more complex pattern matching.