How do I search SQLite text for a pattern with LIKE?

Put % after the starting text. It stands for any sequence of characters, including an empty sequence, so LIKE 'Al%' matches both Al and longer names that begin with Al. [S1]

Here is a small, runnable example using invented names:

CREATE TABLE people (name TEXT);
INSERT INTO people (name) VALUES
  ('Alice'),
  ('Al'),
  ('Malik'),
  ('Ari');

SELECT name
FROM people
WHERE name LIKE 'Al%'
ORDER BY name;

The matching rows are Al and Alice. Malik contains “Al” but does not begin with it, and Ari has a different beginning. The ORDER BY makes the output order explicit.

Use _ for exactly one character. For example, name LIKE 'A_i' requires a name with A, then one character, then i. [S1]

Add one invented row and try it:

INSERT INTO people (name) VALUES ('Ami');

SELECT name
FROM people
WHERE name LIKE 'A_i'
ORDER BY name;

The result is Ami and Ari: each has exactly one character between A and i. A name such as Alii would not match this pattern because it has two characters between those letters. If you need a variable number of characters in that position, use % instead of _.

By default, SQLite’s LIKE ignores case differences for ASCII letters, so a pattern such as 'Al%' can match an ASCII name beginning with al as well as one beginning with Al. For Unicode characters beyond ASCII, default matching is case-sensitive. The ICU extension provides broader Unicode case folding. [S1]

For a quick check, add ('alex') to the sample table and run:

SELECT name
FROM people
WHERE name LIKE 'Al%'
ORDER BY name;

alex matches under SQLite’s default ASCII case behavior. The documented behavior does not establish that every Unicode case pair will match, nor that the ICU extension is installed in your particular SQLite setup. If your data includes non-ASCII letters and the result matters, test with representative values in the environment where the query will run.

Start with the part of the text you know, then choose the wildcard for the uncertain part. Keep the example row values beside the query so you can compare the expected matches with the actual output.

Copy and adapt this pattern:

-- Example: find names that start with Jo
SELECT name
FROM people
WHERE name LIKE 'Jo%'
ORDER BY name;

For one required character between known letters, substitute _, as in LIKE 'J_n'. For a beginning match, put % after the known prefix; for a match containing text anywhere, place % on both sides, as in LIKE '%Jo%'. That last pattern matches a substring, not just a prefix. These patterns follow the meanings of % and _ described above. [S1]

A practical check is to run the query against a few rows you can inspect, including one you expect to match and one you expect not to match. If the results differ, check the placement of the wildcards and whether the letters involved are ASCII or Unicode.