Write a query to find all records that have a certain substring within a text column.

Instruction: Given a table 'products' with a column 'description', write a SQL query to find all records where 'description' contains the substring 'USB'.

Context: This question assesses the candidate's ability to use string functions in SQL to perform text searches within a database, an essential skill for data analysis.

Updated

Official answer

For PostgreSQL, explicitly select the deterministic C collation to match the case-sensitive substring USB:

SELECT *
FROM products
WHERE description COLLATE "C" LIKE '%USB%';

The percent wildcards allow any text before or after USB. USB cable and miniUSB match; a NULL description does not. This is a substring search, so it does not require USB to be a separate word. PostgreSQL ILIKE is an option for a case-insensitive pattern under the active locale; LIKE behavior also depends on collation; PostgreSQL 18 can use case-insensitive nondeterministic collations. Other engines have their own rules.

For user-supplied text, bind the pattern as a parameter and escape percent/underscore when they should be literal characters. Parameterization prevents values becoming SQL syntax but does not remove their pattern-wildcard meaning.

A leading wildcard generally does not fit an ordinary prefix B-tree lookup. Evaluate an appropriate text-search index for the required semantics and workload. Full-text word search is not automatically equivalent to arbitrary substring matching.

Related Questions