LIKE operator and its wildcards in SQL

Date Published: 23/08/2020Published By: JaiSchool

We use LIKE operator and its wildcard with WHERE clause to search for a pattern match in a column.

Wildcards to use with LIKE

WildcardsDefinitionExample
"s{084fae9a6ee6da5a140e203e4160c172c943a2d77bb1f312784b18f1e0e553dc}"'S' at First PositionSee
"{084fae9a6ee6da5a140e203e4160c172c943a2d77bb1f312784b18f1e0e553dc}s"'S' at Second PositionSee
"{084fae9a6ee6da5a140e203e4160c172c943a2d77bb1f312784b18f1e0e553dc}s{084fae9a6ee6da5a140e203e4160c172c943a2d77bb1f312784b18f1e0e553dc}"'S' at Any PositionSee
"_s{084fae9a6ee6da5a140e203e4160c172c943a2d77bb1f312784b18f1e0e553dc}"'S' at Second PositionSee
"s{084fae9a6ee6da5a140e203e4160c172c943a2d77bb1f312784b18f1e0e553dc}g"'S' at First Position &
'g' at Second Position
See
"[abc]{084fae9a6ee6da5a140e203e4160c172c943a2d77bb1f312784b18f1e0e553dc}"Strings that start from a or b or cSee
"[s-g]{084fae9a6ee6da5a140e203e4160c172c943a2d77bb1f312784b18f1e0e553dc}"Strings that start from a to gSee
"so{084fae9a6ee6da5a140e203e4160c172c943a2d77bb1f312784b18f1e0e553dc}"s at first pos. and o at second posSee
"s_n{084fae9a6ee6da5a140e203e4160c172c943a2d77bb1f312784b18f1e0e553dc}"s at first pos and n at third posSee

Understand it with example.

Make a database by running query

CREATE DATABASE jai_school;

Create a table in the database

CREATE TABLE mobiles(brand_name VARCHAR(30),price INT(10));

Insert data in the table

INSERT INTO mobiles(brand_name,price)VALUES("Samsung",80000);
INSERT INTO mobiles(brand_name,price)VALUES("Apple",140000);
INSERT INTO mobiles(brand_name,price)VALUES("Nokia",25000);
INSERT INTO mobiles(brand_name,price)VALUES("MI",14000);
INSERT INTO mobiles(brand_name,price)VALUES("Honor",9000);
INSERT INTO mobiles(brand_name,price)VALUES("Sony",18000);
INSERT INTO mobiles(brand_name,price)VALUES("Realme",14000);
INSERT INTO mobiles(brand_name,price)VALUES("Oneplus",28000);
INSERT INTO mobiles(brand_name,price)VALUES("Motorola",13000);

Table Image:

mysql table image

Examples-

example-1

s at first position

SELECT * FROM mobiles WHERE brand_name LIKE "s{084fae9a6ee6da5a140e203e4160c172c943a2d77bb1f312784b18f1e0e553dc}";

example-2

s at last position

SELECT * FROM mobiles WHERE brand_name LIKE "{084fae9a6ee6da5a140e203e4160c172c943a2d77bb1f312784b18f1e0e553dc}s";

example-3

a at any position

SELECT * FROM mobiles WHERE brand_name LIKE "{084fae9a6ee6da5a140e203e4160c172c943a2d77bb1f312784b18f1e0e553dc}a{084fae9a6ee6da5a140e203e4160c172c943a2d77bb1f312784b18f1e0e553dc}";

example-4

a at second position

SELECT * FROM mobiles WHERE brand_name LIKE "_a{084fae9a6ee6da5a140e203e4160c172c943a2d77bb1f312784b18f1e0e553dc}";

example-5

m at first position and a at last position

SELECT * FROM mobiles WHERE brand_name LIKE "m{084fae9a6ee6da5a140e203e4160c172c943a2d77bb1f312784b18f1e0e553dc}a";

example-6

brand_name that starts from m or o or t.

SELECT * FROM mobiles WHERE brand_name LIKE "[mot]{084fae9a6ee6da5a140e203e4160c172c943a2d77bb1f312784b18f1e0e553dc}";

example-7

This query will display all records, where brand_name starts a, b, c or d.

SELECT * FROM mobiles WHERE brand_name LIKE '[a-d]{084fae9a6ee6da5a140e203e4160c172c943a2d77bb1f312784b18f1e0e553dc}';

example-8

a at first position and p at second position

SELECT * FROM mobiles WHERE brand_name LIKE 'ap{084fae9a6ee6da5a140e203e4160c172c943a2d77bb1f312784b18f1e0e553dc}';

example-9

h at first position and n at third position

SELECT * FROM mobiles WHERE brand_name LIKE 'h_n{084fae9a6ee6da5a140e203e4160c172c943a2d77bb1f312784b18f1e0e553dc}';