Patients With a Condition

Patients With a Condition

What is this problem about?

The Patients With a Condition SQL problem asks you to find patients who have a condition starting with a specific prefix (e.g., "DIAB1"). The condition field may contain multiple space-separated codes, and the target prefix can appear at any position. This easy SQL problem tests string pattern matching with LIKE or REGEXP. The database interview pattern is demonstrated.

Why is this asked in interviews?

Microsoft, Amazon, Google, and Bloomberg ask this to test SQL string pattern matching — specifically handling multi-value fields in a single column (a common real-world data quality issue). The key challenge is matching the prefix at the start of any space-separated code.

Algorithmic pattern used

SQL LIKE with multiple patterns. Use: WHERE conditions LIKE 'DIAB1%' OR conditions LIKE '% DIAB1%'. This matches the prefix appearing either at the start of the field or after a space. REGEXP alternative: WHERE conditions REGEXP '(^| )DIAB1'.

Example explanation

Patients with conditions: "DIAB100 MYOP", "ACNE DIAB1234", "DIAB1".

• "DIAB100 MYOP": starts with DIAB1 ✓.
• "ACNE DIAB1234": DIAB1 appears after space ✓.
• "DIAB1": exact match ✓. All three match. Return all three patients.

Common mistakes candidates make

• Using LIKE '%DIAB1%' (matches "ADIAB1" which is not a valid prefix of a code).
• Not handling the first code case separately from codes after spaces.
• Case sensitivity issues (use UPPER() if needed).
• Using INSTR or LOCATE incorrectly for multi-position matching.

Interview preparation tip

Multi-value field queries require careful prefix matching. The two-condition LIKE approach handles both "starts at beginning" and "starts after space." REGEXP is cleaner: '(^| )PREFIX'. Practice this pattern for: "emails containing a domain," "tags starting with a prefix," and similar space/comma-separated field queries. REGEXP expertise is highly valued in data engineering SQL interviews.