[Edit]

MySQL - LIKE operator

1 contributors

2 contributions

0 discussions

3 points

Created by:

maxsior322

347

In this article, we would like to show you how to use LIKE operator in MySQL.

Quick solution:

xxxxxxxxxx
 
SELECT `column_1`, `column_2`, `column_3`, `column_n`
FROM `table_name`
WHERE `column_n` LIKE 'pattern';  /* or any other column */

We use LIKE operator in a WHERE clause to find rows that contan column cell that matches indicated pattern.

There are two wildcards used with the LIKE operator:

% - matches 0 or more characters (0, 1, 2, 3, ...),
_ - matches any single character (requres in _ place some character).

LIKE operator	Description
xxxxxxxxxx 1 WHERE `name` LIKE 'prefix%'	Finds any rows that `name` cell starts with `prefix` text.
xxxxxxxxxx 1 WHERE `name` LIKE '%postfix'	Finds any rows that `name` cell ends with `postfix` text.
xxxxxxxxxx 1 WHERE `name` LIKE '%middle%'	Finds any rows that `name` cell has `middle` text somewhere inside.
xxxxxxxxxx 1 WHERE `name` LIKE 'part1%part2%part3'	Finds any rows that `name` cell: starts with `part1` text and has `part2` text somewhere inside and ends with `part3` text.

Practical example

Edit

To understand LIKE operator patterns better way, look at the below examples:

xxxxxxxxxx
 
[ PATTERN ]    [ EXAMPLE MATCHED TEXTS ]
​
  J%            John     J  Jooohn  etc.
  Jo%           John    Jo  Jooohn  etc.
    %n          John     n      an  etc.
   %hn          John    hn     ahn  etc.
   %h%          John     h     aha  etc.
  %oh%          John    oh    aoha  etc.
  J___          John  Jaaa    Jbbb  etc.
  Jo__          John  Joaa    Jobb  etc.
  ___n          John  aaan    bbbn  etc.
  __hn          John  aahn    bbhn  etc.
  _oh_          John  aoha    bohb  etc.

To show how the LIKE operator works, we will use the following table:

Note:

At the end of this article you can find database preparation SQL queries.

Example

Edit

In this example, we will display Jonh user row using some LIKE operator cases taken from above patterns.

Queries:

xxxxxxxxxx
 
SELECT *
FROM `users`
WHERE `name` LIKE 'J%';

xxxxxxxxxx
 
SELECT *
FROM `users`
WHERE `name` LIKE 'Jo%';

xxxxxxxxxx
 
SELECT *
FROM `users`
WHERE `name` LIKE '%hn';

xxxxxxxxxx
 
SELECT *
FROM `users`
WHERE `name` LIKE '_oh_';

xxxxxxxxxx
 
SELECT *
FROM `users`
WHERE `name` LIKE 'J_h%';

Result:

Database preparation

Edit

Use below queries to prepare database and test LIKE operator.

create_tables.sql file:

xxxxxxxxxx
 
CREATE TABLE `users` (
    `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(50) NOT NULL,
    `surname` VARCHAR(50) NOT NULL,
    `department_id` INT(10) UNSIGNED,
    `salary` DECIMAL(15,2) NOT NULL,
    PRIMARY KEY (`id`)
);

insert_data.sql file:

xxxxxxxxxx
 
INSERT INTO `users`
    ( `name`, `surname`, `department_id`, `salary`)
VALUES
    ('John', 'Stewart', 1, '3512.00'),
    ('Chris', 'Brown', 2, '1344.00'),
    ('Kate', 'Lewis', 3, '6574.00'),
    ('Ailisa', 'Gomez', NULL, '6500.00'),
    ('Gwendolyn', 'James', 2, '4200.00'),
    ('Simon', 'Collins', 4, '3320.00'),
    ('Taylor', 'Martin', 2, '1500.00'),
    ('Andrew', 'Thompson', NULL, '2100.00');

Show discussions ...

MySQL - LIKE operator

Practical example

Example

Database preparation

MySQL

Native Advertising

🚀

Dirask - we help you to

solve coding problems.

Ask question.

❤️💻 🙂