LIKE operator and its wildcards in SQL

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

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

Wildcards to use with LIKE

WildcardsDefinitionExample
"s%"'S' at First PositionSee
"%s"'S' at Second PositionSee
"%s%"'S' at Any PositionSee
"_s%"'S' at Second PositionSee
"s%g"'S' at First Position &
'g' at Second Position
See
"[abc]%"Strings that start from a or b or cSee
"[s-g]%"Strings that start from a to gSee
"so%"s at first pos. and o at second posSee
"s_n"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%";

example-2

s at last position

SELECT * FROM mobiles WHERE brand_name LIKE "%s";

example-3

a at any position

SELECT * FROM mobiles WHERE brand_name LIKE "%a%";

example-4

a at second position

SELECT * FROM mobiles WHERE brand_name LIKE "_a%";

example-5

m at first position and a at last position

SELECT * FROM mobiles WHERE brand_name LIKE "m%a";

example-6

brand_name that starts from m or o or t.

SELECT * FROM mobiles WHERE brand_name LIKE "[mot]%";

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]%';

example-8

a at first position and p at second position

SELECT * FROM mobiles WHERE brand_name LIKE 'ap%';

example-9

h at first position and n at third position

SELECT * FROM mobiles WHERE brand_name LIKE 'h_n%';

Publish A Comment

Leave a Reply

Your email address will not be published.