Normalize names with portable string functions
Return user ID, a trimmed lowercase name, and its first three characters. Preserve NULL names as NULL. Return only the requested columns and follow the specified ordering. This question probes compose trim, lower, and substring; the comma-argument substring form works in sqlite, mysql 8, and postgresql.
Input
Setup script defining users(id INT, name VARCHAR(50)).
Output
id, normalized_name, prefix; ascending id.
Constraints
0–10000 rows per table. IDs are unique and non-NULL unless stated otherwise; foreign keys may be NULL. Numeric amounts are integers from 0 to 1000000. Dates are valid YYYY-MM-DD text. Text uses case-sensitive comparison; test data has consistent case. Duplicate business values and NULLs are allowed unless excluded by the task.
Example
Tables and sample rows
CREATE TABLE users(id INT, name VARCHAR(50)); INSERT INTO users VALUES (1,' ALICE '),(2,' Bo '),(3,NULL);
Expected result
id | normalized_name | prefix 1 | alice | ali 2 | bo | bo 3 | NULL | NULL
Another dataset (1)
CREATE TABLE users(id INT, name VARCHAR(50)); INSERT INTO users VALUES (1,''),(2,' A '),(3,'Mixed');
Expected result
id | normalized_name | prefix 1 | | 2 | a | a 3 | mixed | mix
Covers missing matches, ties, or NULL values without relying on insertion order.
Hints (2)
- Trim before lowercasing.
- Take the prefix from the normalized value.
Target complexity: time O(n) · space O(1)