Handles Breaking the Username Policy

Easy
427.

Handles Breaking the Username Policy

EasyStringsBasics

MySQL

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

ColumnType
user_idint
handlevarchar
created_atdate

Primary key: user_id.

One row per account. Handles are lower-case letters, digits, ., _ and (on legacy accounts) spaces.

Example

The rows must come back in the order shown.

users

user_idhandlecreated_at
1priya.sharma2022-05-10
29to5trader2022-07-01
3dev2022-08-19
4rahul verma2022-09-03
5kc2022-11-21
6the.official.mumbai.foodie2023-01-14
7neha_20032023-02-02

Output

user_idhandle
29to5trader
4rahul verma
5kc
6the.official.mumbai.foodie
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
user_idint
handlevarchar
created_atdate

Primary key: user_id.

One row per account. Handles are lower-case letters, digits, ., _ and (on legacy accounts) spaces.

Examples

Example 1

users

user_idhandlecreated_at
1priya.sharma2022-05-10
29to5trader2022-07-01
3dev2022-08-19
4rahul verma2022-09-03
5kc2022-11-21
6the.official.mumbai.foodie2023-01-14
7neha_20032023-02-02

Output

user_idhandle
29to5trader
4rahul verma
5kc
6the.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, or NOT 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, ' ') > 0 or LIKE '% %' 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_id

Another 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_id

Another 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 →