Patients With a Condition | LeetCode 1527

This chapter covers LeetCode problem 1527 ("Patients With a Condition"), focusing on pattern matching in SQL using the LIKE operator, wildcard characters (%), and handling multi-word string attributes in relational tables.

1. Problem Statement and Status Quo

In this problem, we are managing a database for a hospital. We are provided with a single table named patients which stores information about hospitalized individuals and their medical conditions.

Table Schema: patients

• patient_id (INT): Primary key identifying each patient uniquely.
• patient_name (VARCHAR): Name of the patient.
• conditions (VARCHAR): Contains zero or more medical condition codes separated by spaces.

Objective

Write a SQL query to find all columns (patient_id, patient_name, and conditions) for patients who have Type 1 Diabetes.

The Medical Rule

• Type 1 Diabetes is always identified by the exact prefix code DIAB1.
• The result table can be returned in any order.

2. Core Mental Model and Real-World Analogy

Real-Life Analogy: Searching for Keyword Tags in a Visitor Log

Imagine you are looking through a security visitor log where every visitor writes down a space-separated list of items they are carrying in the "Items" column. You are tasked with finding anyone carrying a specific restricted item code, such as DIAB1.

However, a person might carry multiple items:

• They might list DIAB1 as the very first item they write down.
• Or, they might list another item first, followed by a space, and then DIAB1 as their second or third item.
• They would not carry DIAB1 if it is embedded as a substring inside a completely different word (e.g., matching the letters inside another code). It must appear as a distinct word prefix.

3. Analyzing the Sample Data

Let us inspect the sample input and trace which rows qualify for the final output.

Deduuced Structural Rules

From our analysis, DIAB1 can appear in two valid positions within the conditions string:

1. At the absolute beginning of the string (e.g., "DIAB1 CNE").
2. As a subsequent word preceded by a blank space (e.g., "ACNE DIAB1").

4. Naive vs. Better Approach

The Naive Approach (Incorrect)

If we simply search for DIAB1 anywhere inside the string without accounting for word boundaries:

SELECT * 
FROM patients 
WHERE conditions LIKE '%DIAB1%';

Note: While this catches strings where DIAB1 is at the beginning or middle, it also incorrectly matches strings where DIAB1 is buried inside another unrelated medical code (e.g., if a code like XDIAB1 or MODIAB1 ever existed). Furthermore, it fails to explicitly enforce the prefix constraint taught in the medical rule.

The Correct Multi-Condition Approach

To strictly enforce that DIAB1 is a valid prefix (either at the start of the field or after a space), we must combine two separate LIKE conditions using the OR operator:

1. conditions LIKE 'DIAB1%': Matches if the string starts with DIAB1.
2. conditions LIKE '% DIAB1%': Matches if the string contains a space followed immediately by DIAB1.

5. SQL Implementation & Step-by-Step Evolution

Here is the complete, working SQL query implemented for MySQL:

SELECT patient_id, patient_name, conditions
FROM patients
WHERE conditions LIKE 'DIAB1%' 
   OR conditions LIKE '% DIAB1%';

Alternatively, using SELECT * as demonstrated in the lecture:

SELECT *
FROM patients
WHERE conditions LIKE 'DIAB1%' 
   OR conditions LIKE '% DIAB1%';

6. Deep Dive into SQL LIKE and Wildcards (% vs _)

The LIKE operator in SQL is used within a WHERE clause to search for a specified pattern in a column. To build flexible patterns, SQL provides wildcard characters:

How Our Two Patterns Work:

1. 'DIAB1%':
   • Starts strictly with DIAB1.
   • The % at the end allows any number of characters (or no characters at all) to follow after DIAB1.
2. '% DIAB1%':
   • The leading % allows any arbitrary characters to precede the pattern.
   • We explicitly include a space character (' ') right before DIAB1 to guarantee that DIAB1 acts as a word prefix, rather than a mid-word substring.
   • The trailing % allows any characters or codes to follow after DIAB1.

7. Summary and Key Takeaways

• Problem Domain: Filtering text columns in SQL based on specific token prefixes using pattern matching.
• Table Structure: The patients table contains primary key patient_id, patient_name, and a space-separated conditions string.
• The Core Keyword: Type 1 Diabetes is uniquely identified by the prefix DIAB1.
• The LIKE Operator: Essential for pattern-based searches in SQL WHERE clauses.
• Wildcard Power: The percentage sign (%) represents zero or more arbitrary characters, making it ideal for variable-length suffix matching.
• Handling Word Boundaries: To ensure DIAB1 is a true prefix of a word (and not part of another word), check both the start of the string ('DIAB1%') and after a space ('% DIAB1%').
• Logical Operators: Combine multiple pattern checks cleanly using the OR operator.
• Query Cleanliness: Grouping conditions with parentheses and formatting multi-line SQL statements improves readability and long-term maintainability in production environments.