10.3 How to Search Inside Text with LIKE and instr()

By Ravindra Bagale Last updated: 31 October 2026

Why. Analysts often need rows whose text contains a word, such as every customer with Traders in the name. You also need to know where a character sits inside a code.

What. LIKE with % finds text that contains a pattern; % means any number of characters. In SQLite, LIKE ignores capital and small letters only for plain English letters, so 'TRADERS' still matches Traders, but É does not match é. Many other databases treat LIKE differently, for example PostgreSQL is case-sensitive. instr(text, part) returns the position of the first match, counting from 1, or 0 when the part is not found.

How. Search customer names with LIKE, test an accented letter, and find the first dash in tracking codes with instr().

Steps

Run the first example and see that Kavya Traders is found with capital TRADERS.
Run the second example: 1 means true and 0 means false.
Run the third example and read the dash position and the 0 for a missing letter.
Remember that a LIKE with % at the start has to check every row, so it can be slow on very big tables.

Examples

Example 1 · dataset: swiftship_logistics

SELECT customer_name
FROM customers
WHERE customer_name LIKE '%TRADERS%';

Output

customer_name
Kavya Traders

Meaning. SQLite found Kavya Traders even though the pattern used capital letters, because LIKE ignores case for English letters.

Example 2 · dataset: swiftship_logistics

SELECT raw_name,
       raw_name LIKE '%ÉLODIE%' AS match_capital_e,
       raw_name LIKE '%élodie%' AS match_same_e
FROM agents
WHERE agent_id = 5;

Output

raw_name	match_capital_e	match_same_e
élodie martin	0	1

Meaning. The capital É pattern gives 0, so it does not match. The same small é gives 1. Clean or standardise such names before you search them.

Example 3 · dataset: swiftship_logistics

SELECT tracking_code,
       instr(tracking_code, '-') AS first_dash,
       instr(tracking_code, 'Z') AS position_of_z
FROM shipments
WHERE shipment_id IN (1001, 1004);

Output

tracking_code	first_dash	position_of_z
PNQ-01-00017	4	0
DXB-01-00009	4	0

Meaning. The first dash is the 4th character in both codes. There is no Z in either code, so instr() returns 0.

Ravindra Bagale's Tip

To find text that does not contain a word, use NOT LIKE. Rows where the column is NULL are not returned by LIKE or NOT LIKE, so check them separately with IS NULL.