Brightlane's search index builder normalizes contributor entries by lowercasing and trimming an author name, then combining the cleaned name with a publication year using '|' as the separator.
Write a query to return the search key for the author ' DR. JAMES WATSON ' and the year '1953'.
Output:
- A single row with one column,
search_key, containing the constructed key in the form<lowercased-trimmed-name>|<year>.
Schema · ecommerce 5 tables
Run previews · Check grades
Write a query, then run it to see results here.
Worked solution Try it yourself first
SELECT
CONCAT_WS('|', LOWER(TRIM(' DR. JAMES WATSON ')), '1953') AS search_keyThe shape
LOWER(TRIM(' DR. JAMES WATSON ')) runs the two cleanup transforms before the value reaches the concatenation, so what gets joined is the lowercased, trimmed form and not the raw input. CONCAT_WS('|', ..., '1953') then places a single '|' between the cleaned name and the year, producing the search key 'dr. james watson|1953'.
Clause by clause
SELECT CONCAT_WS('|', LOWER(TRIM(' DR. JAMES WATSON ')), '1953') AS search_keyevaluates the full expression and labels the resulting columnsearch_key. The argument order matters:'|'is the separator; the second argument is the cleaned name; the third is the year.- The cleanup runs inside out.
TRIM(' DR. JAMES WATSON ')strips the leading and trailing spaces and returns'DR. JAMES WATSON'.LOWER(...)then lowercases that result to'dr. james watson'. Only that final cleaned string is handed toCONCAT_WS. CONCAT_WS('|', 'dr. james watson', '1953')joins the two non-NULL values with one'|'between them and returns'dr. james watson|1953'. There is noFROMbecause the inputs come straight from the prompt as literals.
Why composing inside CONCAT_WS and not cleaning afterward
Running the transforms on the input before the concatenation keeps the cleanup local to the value that needs it. Concatenating first and then trying to lowercase or trim the combined string would corrupt the year side too, or would require carving the string back apart to apply transforms only to one piece. The cleaner shape is to clean each component on its own and let the concatenation see only the final forms.
The cleanup order itself matters as well. LOWER(TRIM(x)) and TRIM(LOWER(x)) happen to produce the same result on this input, but the conventional shape is to trim first so any subsequent transforms operate on the value's real content rather than on padding.
The trap
It is tempting to read the expression as TRIM(LOWER(...)) or even to skip one transform thinking the other will cover it. Neither holds. LOWER does not strip whitespace, and TRIM does not change case. A search key built from LOWER alone would carry the padding spaces into the index and never match a lookup; one built from TRIM alone would still record the all-caps form. Both transforms are doing distinct work, and dropping either breaks the lookup the index is being built for.
You practiced composing CONCAT_WS(separator, LOWER(TRIM(...)), value) — chain string transforms inside a CONCAT_WS call so the cleaned form is what gets concatenated, not the raw input.
