Text search, normalization and literal patterns

A text predicate needs a matching contract. The worked query searches for the ASCII substring engineer after applying LOWER to the stored title. It returns identities 1, 2, 4, 6, 7 and 8. The fixture deliberately contains both Engineer and Engineering.

This is substring matching, not linguistic search, tokenization or a relevance ranking. Unicode case rules, collations and accent handling vary across engines and environments. State the simple ASCII teaching scope rather than promising that LOWER makes every human-language search equivalent.

LIKE treats percent as any sequence and underscore as one character. Surrounding engineer with percent permits text before and after it. LOWER transforms the compared expression; it does not permanently rewrite the source title. The literal search value is lower-case to match that expression.

If a user wants a literal percent or underscore, escaping the pattern is a separate task from binding the value safely. Parameters protect value boundaries but do not automatically change wildcard meaning. A later lesson demonstrates bound values explicitly in both drivers.

The counterexample compares the whole normalized title with engineer. No fixture title is exactly that word, so equality returns no rows. This is a semantic mismatch rather than an engine failure. The intended substring contract must determine the operator.

Avoid assuming that a leading-percent search will use a conventional prefix index efficiently. A functional expression may also require an appropriate index or search strategy. Correct matching comes first; explain actual plan evidence before promising performance for a particular pattern.

Find titles whose normalized text starts with data, including a trailing space after the word. Predict identities 3 and 6. This small task changes the pattern from a general substring to a prefix and keeps the result ordered by identity.

Write the normalized expression and the pattern separately before joining them with LIKE. The space prevents a hypothetical Database title from matching the word Data accidentally. Document that distinction instead of silently changing a user-facing search contract.

Step 1: Normalize the title expression. Step 2: Use a prefix pattern beginning with data and a space.

The solution uses data followed by a space and percent. It returns Data Analyst and Data Engineer, preserving their separate identities. A test should include a nonmatching title and a near-prefix counterexample when generalizing beyond the fixed fixture.

In application code, send the complete intended pattern as a bound value rather than concatenating untrusted text into SQL. Keep wildcard escaping, case behavior and SQL injection prevention as separate decisions. This lesson uses fixed literals so the query itself stays readable.

When discussing search in an interview, distinguish exact equality, prefix matching, substring matching and full-text search. They answer different questions and may need different indexes. Mention the engine and collation when asked about non-ASCII comparison rather than claiming universal normalization behavior.

You now have explicit output shape, population, order and matching semantics. The next lesson introduces date boundaries using a deliberately declared storage format, because implicit timestamp conversions can otherwise hide another class of incorrect filters.

Optional stretch — after the core task works: Add a literal underscore search with an explicit escape character and verify it on both engines.

Computer practice: https://applydjinn.com/typorithm/sql-lab-20261105.zip. Load the shared setup.sql fixture before running a snippet. The local verifier compares exact ordered row arrays in SQLite and PostgreSQL, keeps SQL values separate from driver bindings and never connects to the production website database.