String Functions Quiz

This page reviews all 8 questions from the String Functions quiz, with the correct answer explained clearly, including why the other options are wrong. Use this as a quick revision page before interviews, or to double check your understanding after going through the main String Functions notes.

Question 1

SELECT REPLACE(UPPER(TRIM(' hello world ')), ' ', '_');

Options

• 'hello_world'
• 'HELLO_WORLD'
• ' HELLO_WORLD '
• 'HELLO WORLD'

Correct Answer: 'HELLO_WORLD'

Explanation This question tests whether you can trace nested function calls correctly, working from the inside out.

1. TRIM(' hello world ') removes the leading and trailing spaces, giving 'hello world'.
2. UPPER('hello world') converts every letter to uppercase, giving 'HELLO WORLD'.
3. REPLACE('HELLO WORLD', ' ', '_') replaces the space in the middle with an underscore, giving 'HELLO_WORLD'.

Why the other options are wrong

• 'hello_world' is wrong because it skips the UPPER step entirely, this would be the answer only if UPPER wasn't in the query.
• ' HELLO_WORLD ' is wrong because it assumes TRIM never ran, keeping the leading and trailing spaces, but TRIM is applied first, so those spaces are already gone before UPPER or REPLACE even touch the string.
• 'HELLO WORLD' is wrong because it stops one step early, forgetting that REPLACE still needs to swap the space for an underscore.

Question 2

SELECT CONCAT_WS('-', 'SQL', NULL, 'Functions');

Options

• 'SQL--Functions'
• 'SQL-Functions'
• 'SQL NULL Functions'
• NULL

Correct Answer: 'SQL-Functions'

Explanation CONCAT_WS() (With Separator) is specifically built to skip over NULL values while joining strings. It only places the separator (-) between the actual non-NULL pieces that remain, so the NULL in the middle is simply dropped, not replaced with anything, and no extra separator is added for it.

Why the other options are wrong

• 'SQL--Functions' is wrong because it assumes the NULL still "takes up a spot," causing two separators to appear back to back. This would only happen if NULL were treated as an empty string instead of being skipped entirely.
• 'SQL NULL Functions' is wrong because it assumes NULL gets converted into the literal text "NULL" and inserted into the string, which is not how CONCAT_WS handles NULL.
• NULL is wrong because that's how plain CONCAT() behaves (any NULL argument poisons the whole result), not CONCAT_WS(), which is the whole reason CONCAT_WS exists as a separate function.

Question 3

Which of the following queries correctly extracts the last three characters of a column username?

Options

• SELECT LEFT(username, 3) FROM users;
• SELECT RIGHT(username, 3) FROM users;
• SELECT SUBSTRING(username, LENGTH(username)-2, 3) FROM users;
• SELECT POSITION(username, 3) FROM users;

Correct Answer: SELECT RIGHT(username, 3) FROM users;

Explanation RIGHT(username, 3) directly returns the last three characters, so it is the clearest answer. A SUBSTRING solution can also work, but for character positions it should use CHAR_LENGTH(username), not MySQL's byte-counting LENGTH(username).

Why the other options are wrong

• LEFT(username, 3) grabs the first 3 characters, not the last 3, this answers a completely different question.
• SUBSTRING(username, LENGTH(username)-2, 3) can start at the wrong position for multi-byte characters because MySQL LENGTH() counts bytes. Use CHAR_LENGTH() for character positions.
• POSITION(username, 3) isn't valid usage here, POSITION (or LOCATE) is meant to search for where a substring appears inside a string, not to slice out a fixed number of characters.

Worth noting: SUBSTRING(username, CHAR_LENGTH(username) - 2, 3) is a valid character-aware alternative.

Question 4

How can you efficiently search for case-insensitive matches in SQL?

Options

• SELECT * FROM users WHERE LOWER(name) = 'john';
• SELECT * FROM users WHERE name LIKE '%John%';
• SELECT * FROM users WHERE name ILIKE 'john';
• SELECT * FROM users WHERE name REGEXP '(?i)john';

Correct Answer for PostgreSQL: SELECT * FROM users WHERE name ILIKE 'john';

Explanation ILIKE is PostgreSQL-specific and directly expresses a case-insensitive match there. MySQL does not support ILIKE; whether = or LIKE is case-insensitive depends on the column's collation. Efficiency depends on indexes, collation, expression indexes where available, and the actual query plan, so no option is universally the fastest across databases.

Why the other options are wrong

• LOWER(name) = 'john' does work for case-insensitive matching, but applying a function like LOWER() directly on the column inside a WHERE clause can prevent the database from using an index efficiently on that column, since LOWER() has to be computed for every row before comparing. This makes it a working answer, but its efficiency depends on whether the database can use a suitable functional or expression index.
• name LIKE '%John%' may be case-sensitive or case-insensitive depending on the database and collation, and it also checks for "John" appearing anywhere in the name, not an exact case-insensitive match, so this changes the meaning of the search entirely.
• name REGEXP '(?i)john' uses inline regex flags for case-insensitivity, but this specific syntax (?i) is not supported in MySQL's REGEXP by default, this flag style is more common in other regex engines, so this option would likely cause unexpected behavior or an error depending on the database.

Question 5

SELECT * FROM users WHERE name LIKE '_A%';

Options

• Returns names that start with "A"
• Returns names where "A" is the second character
• Returns names that end with "A"
• Returns names that contain "A" anywhere

Correct Answer: Returns names where "A" is the second character

Explanation The underscore (_) wildcard matches exactly one character, and the percent (%) wildcard matches any number of characters after that. So '_A%' means: any single character, followed by the letter "A," followed by anything else (or nothing at all). This means the letter "A" must specifically be the second character in the name.

Why the other options are wrong

• "Starts with A" would require the pattern 'A%', with no underscore before the A.
• "Ends with A" would require the pattern '%A', with the wildcard at the start instead of the end.
• "Contains A anywhere" would require the pattern '%A%', with wildcards on both sides.

Question 6

How would you match email addresses that end with .com using REGEXP?

Options

• SELECT email FROM users WHERE email LIKE '%.com';
• SELECT email FROM users WHERE email REGEXP '\\.com$';
• SELECT email FROM users WHERE email REGEXP '[.]com';
• SELECT email FROM users WHERE email LIKE '%.com%';

Correct Answer: SELECT email FROM users WHERE email REGEXP '\\.com$';

Explanation In regular expressions, a plain dot (.) normally means "any single character," not a literal period. To match an actual period character, it needs to be escaped, which is why \\. appears here (the double backslash is how MySQL represents a single literal backslash inside a string, which then escapes the dot in the regex itself). The $ symbol anchors the match to the end of the string, ensuring ".com" is specifically at the end, not just anywhere in the email.

Why the other options are wrong

• email LIKE '%.com' would actually work correctly too, since LIKE doesn't treat the dot as a special wildcard character, only % and _ are wildcards in LIKE. However, the question specifically asks for a REGEXP-based solution, so this doesn't answer what was asked, even though it produces a similar result using a different tool.
• email REGEXP '[.]com' uses a character class [.] to correctly match a literal dot (this part is actually fine), but it's missing the $ anchor at the end, meaning it would match ".com" appearing anywhere in the email, not specifically at the end, which could wrongly match something like "example.comfake@test.org" if "com" appeared mid-string in some unusual email format.
• email LIKE '%.com%' uses wildcards on both sides, meaning it checks if ".com" appears anywhere in the email at all, not specifically at the end, which is too loose a match for this specific requirement.

Question 7

SELECT LENGTH(REPLACE(TRIM(' SQL Functions '), ' ', ''));

Options

• 14
• 12
• 15
• 16

Correct Answer: 12

Explanation Working from the inside out:

1. TRIM(' SQL Functions ') removes the leading and trailing spaces, giving 'SQL Functions' (this string is 13 characters long: S-Q-L-space-F-u-n-c-t-i-o-n-s).
2. REPLACE('SQL Functions', ' ', '') removes the one remaining space in the middle (replacing it with nothing), giving 'SQLFunctions'.
3. LENGTH('SQLFunctions') counts the characters: S-Q-L-F-u-n-c-t-i-o-n-s, which is exactly 12 characters.

Why the other options are wrong

• 14 would be the length if the original leading and trailing spaces were never trimmed at all (2 extra spaces added to 12 gives 14).
• 15 doesn't correspond cleanly to any step in this calculation, it looks like a distractor value close to the others.
• 16 would be roughly the length of ' SQL Functions ' including all three spaces (leading, trailing, and middle) without applying TRIM or REPLACE at all, since the full original string ' SQL Functions ' is 15 characters, so even 16 slightly overshoots that.

Why this question matters: it's a great test of whether you can mentally trace nested function calls correctly, step by step, instead of guessing based on the general "shape" of the query.

Question 8

In an e-commerce system, how would you extract the first 3 characters of a product SKU?

Options

• SELECT LEFT(sku, 3) FROM products;
• SELECT RIGHT(sku, 3) FROM products;
• SELECT LENGTH(sku, 3) FROM products;
• SELECT POSITION(sku, 3) FROM products;

Correct Answer: SELECT LEFT(sku, 3) FROM products;

Explanation LEFT(str, n) is specifically designed to grab the first n characters from the start of a string, which is exactly what's being asked for here.

Why the other options are wrong

• RIGHT(sku, 3) grabs the last 3 characters instead of the first 3, the opposite end of the string.
• LENGTH(sku, 3) isn't even valid syntax in most databases, LENGTH() normally only takes a single string argument and returns its length, it doesn't accept a second argument to slice anything.
• POSITION(sku, 3) misuses POSITION, which is meant to search for where a substring appears inside a string, not to extract a fixed number of characters from a known position.

Quick Recap Table