EVIKA.ACADEMY›SQL Tutorial

SQL LIKE

Chapter 17 of 40 · Estimated read time: 6 min

The LIKE operator searches for a pattern inside a text column. Instead of matching an exact value, it matches any string that fits the pattern — using two wildcard characters: % and _.

%
Percent — zero or more characters
'W%' → starts with W
'%Chair' → ends with Chair
'%USB%' → contains USB anywhere
_
Underscore — exactly one character
'C_1' → C, any char, then 1
'__ab' → 2 chars then ab
'_ohan' → any 1st char, then ohan
SELECT column1, column2 FROM table_name WHERE column LIKE 'pattern';

Example 1 — Products Starting with a Letter

-- Products whose name starts with 'W'
SELECT product_name, price
FROM products
WHERE product_name LIKE 'W%';
product_nameprice
Wireless Mouse899

Example 2 — Products Ending with a Word

-- Products whose name ends with 'Hub'
SELECT product_name, category, price
FROM products
WHERE product_name LIKE '%Hub';
product_namecategoryprice
USB-C HubElectronics1499

Example 3 — Products Containing a Word Anywhere

-- Products that contain 'a' anywhere in their name
SELECT product_name
FROM products
WHERE product_name LIKE '%a%';
product_name
Wireless Mouse
Notebook A5
Ballpoint Pens
Desk Lamp
HDMI Cable

Example 4 — Underscore for Exact Character Count

-- Customer IDs that are exactly 3 characters: C then any 2 digits
SELECT customer_id, customer_name
FROM customers
WHERE customer_id LIKE 'C__';

-- Find customers whose name is exactly 10 characters long
SELECT customer_name
FROM customers
WHERE customer_name LIKE '__________';  -- 10 underscores
C__ matches C01, C02, C03, C04, C05
customer_idcustomer_name
C01Amit Verma
C02Priya Sharma
C03Rohan Mehta
C04Sneha Gupta
C05Arjun Singh

NOT LIKE — Exclude a Pattern

-- Products that do NOT contain 'Cable' in their name
SELECT product_name, category
FROM products
WHERE product_name NOT LIKE '%Cable%';
product_namecategory
Wireless MouseElectronics
USB-C HubElectronics
Notebook A5Stationery
Ballpoint PensStationery
Desk LampFurniture
Ergonomic ChairFurniture

Combining Multiple LIKE Conditions

-- Products starting with 'W' OR starting with 'D'
SELECT product_name, price
FROM products
WHERE product_name LIKE 'W%'
   OR product_name LIKE 'D%';
product_nameprice
Wireless Mouse899
Desk Lamp1199
Case sensitivity: LIKE is case-insensitive in MySQL by default (LIKE 'w%' finds "Wireless Mouse"). In PostgreSQL, LIKE is case-sensitive — use ILIKE for case-insensitive matching. In SQL Server, it depends on the collation setting.
Performance note: A leading % like LIKE '%word' cannot use an index — the database must scan every row. A trailing % like LIKE 'word%' can use an index. For large tables, prefer full-text search for "contains" queries.
✏️ Practice Exercises
1. Find all customers whose name contains 'a' (case-insensitive).
2. Find all products whose name starts with 'B' or ends with 's'.
3. Find customers whose city is exactly 5 characters long.
Show Answers
-- 1.
SELECT customer_name FROM customers
WHERE customer_name LIKE '%a%';   -- case-insensitive in MySQL

-- 2.
SELECT product_name FROM products
WHERE product_name LIKE 'B%'
   OR product_name LIKE '%s';

-- 3.
SELECT customer_name, city FROM customers
WHERE city LIKE '_____';   -- exactly 5 underscores