Poisoning Cases at the Vet Clinic

A veterinary clinic records each visit's findings as a list of diagnosis codes separated by single spaces, for example DERM2 TOX12 VAX1. Every code that starts with the letters TOX is a poisoning diagnosis (TOX1, TOX12, …). Codes such as DETOX4 or ATOX2 merely contain those letters and are not poisoning codes.

Return every visit that has at least one poisoning code, with the columns visit_id, pet_name, findings (the stored text, unchanged). Rows may be in any order. Visits whose findings are empty or NULL never qualify. If no visit qualifies, return no rows. Use LIKE or string functions; SQLite has no REGEXP.

Tables

clinic_visits — One row per appointment at the clinic.

Example

clinic_visits

Expected output

Rows may be returned in any order. Your query also runs against hidden datasets with tricky edge cases.

SQL Path · Strings & CASE · problem 54 of 70 · SQLite

The idea

Searching for '%TOX%' finds the letters anywhere, including inside DETOX4. What we need is a code that starts with TOX, and a code starts in one of two places: at the beginning of the string, or right after a space.

So two patterns cover every case: findings LIKE 'TOX%' for the first code and findings LIKE '% TOX%' for any later one. The space in the second pattern is the word boundary: DETOX4 has E before the T, not a space, so it does not match.

The alternative avoids the two cases by prepending a space: in ' ' || findings every code is preceded by a space, so a single search for ' TOX' with instr is enough. NULL findings make both conditions NULL, which WHERE treats as false, so those visits drop out without extra code.

Solution

SELECT visit_id, pet_name, findings
FROM clinic_visits
WHERE findings LIKE 'TOX%'
   OR findings LIKE '% TOX%';

Another way

SELECT visit_id, pet_name, findings
FROM clinic_visits
WHERE instr(' ' || findings, ' TOX') > 0;

Why common answers fail

Matches the letters anywhere, so look-alike codes such as DETOX4 and ATOX2 are counted as poisoning.

SELECT visit_id, pet_name, findings FROM clinic_visits WHERE findings LIKE '%TOX%';

Only checks the first code in the list and misses poisoning codes that appear later.

SELECT visit_id, pet_name, findings FROM clinic_visits WHERE findings LIKE 'TOX%';

Requires a space before the code, so a poisoning code at the very start of the list is missed.

SELECT visit_id, pet_name, findings FROM clinic_visits WHERE findings LIKE '% TOX%';

What the interviewer is checking

Checks whether you think about word boundaries when searching inside delimited text, and whether you notice the first-item special case.

Mistakes to avoid

• Using '%TOX%', which also matches codes that only contain the letters.
• Forgetting that the first code has no space before it.
• Expecting LIKE to be case-sensitive: in SQLite it ignores ASCII case (use GLOB 'TOX*' if case matters).

Follow-ups to expect

• Count poisoning visits per pet (wrap the filter in GROUP BY pet_name).
• Return each poisoning code on its own row (split the list with a recursive CTE).
• Why is storing codes in one text column a poor design? (Not first normal form; use a visit_codes table.)

In real work

Legacy systems often store tags, ICD-style codes or feature flags as delimited strings, and analysts regularly need prefix searches over them before the data is normalised.

Practice next