Convert Yes/No answers to true, false or null (charity)
The marketing opt-in column mixes Yes, Y, no, N, blanks and nulls. Turn it into a proper true/false column.
- Level
- beginner
- Time
- ~7 min
- Task
- Build the query
- Access
- Free — no account needed
Loading the practice workspace…
About this exercise
What you'll practise
Build a logical column from inconsistent text, keeping 'no answer' distinct from 'no'.
Riverside Food Bank (fictional) collects volunteer contacts on paper forms, typed up by different people.
Steps
- Add
Marketing OK: true whenOptInis yes or y (any capitalisation), false when it is no or n, and null whenOptInis empty text or null. - Output
ContactID,Name,Marketing OK(drop the original OptIn column).
Your result must
- Yes/Y/yes → true
- No/N/no → false
- Blank or null → null, not false
Input
- Contacts (table)
- ContactID: text
- Name: text
- OptIn: text (nullable)
Expected output columns
ContactID (text), Name (text), Marketing OK (logical (some rows null))
| ContactID | Name | Marketing OK |
|---|---|---|
| C0001 | Mateo Castillo | true |
| C0002 | Farah Duarte | true |
| C0003 | Idris Yilmaz | false |