Handles Breaking the Username Policy — SQL Strings Problem
A new username policy says a handle must be 3 to 20 characters long, must not start with a digit, and must not contain a space.
- Difficulty: Easy
- Topics: Strings, Basics
- Dialect: MySQL
- Problem: #427
Problem statement
A new username policy says a handle must be 3 to 20 characters long, must not start with a digit, and must not contain a space. Accounts created before the policy are being audited.
Return every account whose handle breaks at least one of the three rules, with the columns user_id and handle, ordered by user_id. Handles never have leading or trailing spaces.
Tables
Table: users
| Column | Type |
|---|---|
| user_id | int |
| handle | varchar |
| created_at | date |
Primary key: user_id.
One row per account. Handles are lower-case letters, digits, ., _ and (on legacy accounts) spaces.
Examples
Example 1
users
| user_id | handle | created_at |
|---|---|---|
| 1 | priya.sharma | 2022-05-10 |
| 2 | 9to5trader | 2022-07-01 |
| 3 | dev | 2022-08-19 |
| 4 | rahul verma | 2022-09-03 |
| 5 | kc | 2022-11-21 |
| 6 | the.official.mumbai.foodie | 2023-01-14 |
| 7 | neha_2003 | 2023-02-02 |
Output
| user_id | handle |
|---|---|
| 2 | 9to5trader |
| 4 | rahul verma |
| 5 | kc |
| 6 | the.official.mumbai.foodie |
How to solve Handles Breaking the Username Policy
Breaking at least one rule is the OR of the three violations, or equivalently NOT (rule1 AND rule2 AND rule3) by De Morgan's law — the second alternative is written that way.
- Length:
CHAR_LENGTH(handle) < 3 OR CHAR_LENGTH(handle) > 20, orNOT BETWEEN 3 AND 20; BETWEEN is inclusive, so lengths 3 and 20 pass. - Leading digit: take the first character with
LEFT(handle, 1)and test it against the ten digits, or let a regular expression anchored at the start ('^[0-9]') do it. - Space:
LOCATE(' ', handle) > 0,INSTR(handle, ' ') > 0orLIKE '% %'all find an interior space.
LENGTH counts bytes and CHAR_LENGTH characters; they agree here because handles are ASCII, but CHAR_LENGTH is the right habit for user-visible lengths. String functions on every row force a full scan, which is fine for a one-off audit; a policy enforced at sign-up would use a CHECK constraint instead.
Reference solution (MySQL)
SELECT user_id, handle
FROM users
WHERE CHAR_LENGTH(handle) < 3
OR CHAR_LENGTH(handle) > 20
OR LEFT(handle, 1) IN ('0','1','2','3','4','5','6','7','8','9')
OR LOCATE(' ', handle) > 0
ORDER BY user_idAnother way
SELECT user_id, handle FROM users WHERE LENGTH(handle) NOT BETWEEN 3 AND 20 OR handle REGEXP '^[0-9]' OR handle LIKE '% %' ORDER BY user_idAnother way
SELECT user_id, handle FROM users WHERE NOT (CHAR_LENGTH(handle) BETWEEN 3 AND 20 AND SUBSTRING(handle, 1, 1) NOT REGEXP '[0-9]' AND INSTR(handle, ' ') = 0) ORDER BY user_id← Hashtags Used on Three or More Live Posts · Moderation Reports Filed on Weekends in March 2025 →