SQL Quiz: String Functions
10 questions · exam conditions
0:00
String FunctionsQuestion 1 of 10

In the SQL dialect being used, CONCAT treats a NULL argument as an empty string. For one row, first_name is NULL and last_name is ' Ng '. What does the following expression return?

CONCAT(SUBSTRING(first_name, 1, 1), '.', TRIM(last_name))

NULL
.Ng
Ng
. Ng
← Back to quizzes

SQL Quiz

SQL Quiz: String Functions

Practice String Functions in SQL with focused quiz questions that help you check what you know, review explanations, and build confidence with test-style prompts.

What this quiz covers

This quiz focuses on String Functions, giving you a quick way to practice the rules, question types, and explanations that matter most for SQL.

How to use this quiz

Try each quiz question before looking at the correct answer. Use the explanations to review missed ideas, then come back to similar questions until the pattern feels familiar.

All questions

Question 1

In the SQL dialect being used, CONCAT treats a NULL argument as an empty string. For one row, first_name is NULL and last_name is ' Ng '. What does the following expression return?

CONCAT(SUBSTRING(first_name, 1, 1), '.', TRIM(last_name))

  1. NULL
  2. .Ng (correct answer)
  3. Ng
  4. . Ng
Explanation: When a question chains multiple string functions together, your best approach is to evaluate each function from the inside out, tracking how NULL values propagate (or don't) through the expression. Start with SUBSTRING(first_name, 1, 1). Since first_name is NULL, this returns NULL. Next, TRIM(last_name) operates on ' Ng ' and strips the surrounding spaces, returning 'Ng'. Now you feed three arguments into CONCAT: NULL, '.', and 'Ng'. Here's the key rule the question establishes: in this SQL dialect, CONCAT treats NULL as an empty string rather than propagating NULL through the result. So CONCAT(NULL, '.', 'Ng') becomes effectively CONCAT('', '.', 'Ng'), which returns '.Ng'. That makes B the correct answer. Choice A is the classic NULL trap — in standard SQL (and some dialects like older MySQL configurations), any NULL argument to CONCAT makes the entire result NULL. But the question explicitly states this dialect treats NULL as an empty string, so NULL propagation doesn't apply here. Choice C drops the dot entirely, which would only happen if CONCAT somehow ignored the '.' literal — but string literals are never NULL and are always included. Choice D returns '. Ng ' with extra spaces, ignoring the fact that TRIM already removed them. The study tip here: always read the question's stated dialect behavior carefully. NULL handling in string functions is one of the most dialect-dependent behaviors in SQL, and exam questions will often give you the rule explicitly — don't override it with your default assumptions.

Question 2

Assume the dialect supports TRIM(BOTH 'x' FROM value), which repeatedly removes the specified character from both ends but not from the interior. What does this expression return?

CONCAT('[', TRIM(BOTH 'x' FROM 'xxxAxxBxxx'), ']')

  1. [AxxB] (correct answer)
  2. [AB]
  3. [xxAxxBxx]
  4. [xxxAxxBxxx]
Explanation: When working with TRIM in SQL, the critical concept to understand is the difference between edge trimming and global replacement. TRIM(BOTH 'x' FROM value) is a surgical operation — it walks inward from each end, removing the specified character only as long as it keeps finding matches. The moment it hits a non-matching character, it stops completely and leaves the interior untouched. Take 'xxxAxxBxxx' as your input. Starting from the left, the function strips xxx until it hits A — done. From the right, it strips xxx until it hits B — done. What remains is AxxB. Wrapping that with CONCAT('[', ..., ']') produces [AxxB], confirming A is correct. B ([AB]) represents the most common trap: confusing TRIM with a global REPLACE. REPLACE('xxxAxxBxxx', 'x', '') would remove every x everywhere, but TRIM never touches the interior xx. C ([xxAxxBxx]) reflects a miscount — imagining that only one x is removed per side rather than all consecutive leading/trailing xs. D ([xxxAxxBxxx]) suggests no trimming happened at all, as if the function failed entirely, which would only occur if the character argument didn't match. A useful mental model: think of TRIM as peeling an onion from the outside in — it removes layers of the target character until it hits something different, then stops. On exam questions, always ask yourself: is this character at the edge, or in the interior? Edge characters get trimmed; interior ones are always preserved.

Question 3

Assume one-based substring positions and that requesting more characters than remain returns only the available characters. The column token contains ' ABCDE '. What does the following expression return?

CONCAT(SUBSTRING(TRIM(token), 4, 5), SUBSTRING(TRIM(token), 1, 2))

  1. DEAB (correct answer)
  2. DEABC
  3. CDEAB
  4. DE AB
Explanation: When you see nested string functions like this, always work from the inside out. The key operations here are TRIM, then SUBSTRING, then CONCAT. Start with TRIM(token). The column contains ' ABCDE ' — two spaces on each side. TRIM removes all leading and trailing whitespace, leaving 'ABCDE' (5 characters). Now evaluate each SUBSTRING call on 'ABCDE':
  • SUBSTRING('ABCDE', 4, 5) — start at position 4, request 5 characters. Position 4 is D, and only DE remain (2 characters). Per the problem's rule, you get only what's available: 'DE'.
  • SUBSTRING('ABCDE', 1, 2) — start at position 1, take 2 characters: 'AB'.
CONCAT('DE', 'AB') = 'DEAB', which is A. As for the wrong answers: B (DEABC) assumes the first substring returns 3 characters (DEC or similar), ignoring the "only available characters" rule. C (CDEAB) suggests starting at position 3 instead of 4 — a common off-by-one error if you forget that positions are one-based and count incorrectly. D (DE AB) implies TRIM was never applied, leaving spaces in the string so that position 4 of the untrimmed value would land on a space character rather than D. A good habit: write out the intermediate string after each function call before moving to the next one. This prevents compounding errors across nested operations, especially when trimming changes string length and shifts character positions.

Question 4

Assume ordinary TRIM(value) removes spaces only from the beginning and end of a string. The column label contains ' North Ridge ', with two spaces between the words. What does this expression return?

CONCAT(SUBSTRING(TRIM(label), 1, 5), '|', SUBSTRING(TRIM(label), 6, 7))

  1. North|Ridge with no space after the separator
  2. North| Ridge with one space after the separator
  3. North| Ridge with two spaces after the separator (correct answer)
  4. North| Ridge with spaces on both sides
Explanation: When working with nested string functions, always resolve operations from the innermost function outward before applying the outer ones. Here, TRIM(label) is evaluated first. Starting with ' North Ridge ', TRIM strips only the leading and trailing spaces — it does not touch spaces in the middle of the string. The result is 'North Ridge' (12 characters), which still has two spaces between the words. Now apply the two SUBSTRING calls to this trimmed value:
  • SUBSTRING('North Ridge', 1, 5) → starts at position 1, takes 5 characters → 'North'
  • SUBSTRING('North Ridge', 6, 7) → starts at position 6, takes 7 characters → ' Ridge' (two spaces, then "Ridge")
CONCAT joins these with '|' in between, giving 'North| Ridge' — C is correct. A (North|Ridge) is wrong because it assumes both spaces between the words were removed. TRIM only handles leading/trailing whitespace, not internal spaces. B (North| Ridge) reflects a common misconception that only one of the two internal spaces survives — perhaps imagining some partial trimming behavior. In reality, neither internal space is touched. D ( North| Ridge) incorrectly assumes leading spaces were preserved, which would only be true if TRIM hadn't been applied at all. A good strategy: whenever you see TRIM in a nested expression, immediately ask yourself "which spaces does this actually remove?" Remember — standard TRIM is strictly a boundary operation, leaving internal whitespace completely intact.

Question 5

Assume SUBSTRING(value, start, length) uses one-based positions. The column code contains two leading spaces and two trailing spaces: ' Q7X9 '. What does this expression return?

CONCAT('[', TRIM(SUBSTRING(code, 2, 4)), ']')

  1. [ Q7X]
  2. [Q7X9]
  3. [Q7X] (correct answer)
  4. [7X9]
Explanation: When you see nested string functions like this, work from the inside out — evaluate the innermost function first, then apply each outer function to that result. Start with SUBSTRING(code, 2, 4). The full value is ' Q7X9 ' (positions 1–8). Starting at position 2 (one-based) and taking 4 characters gives you positions 2–5: ' Q7X' — that's one space followed by Q7X. Next, TRIM(' Q7X') strips the leading space, leaving 'Q7X'. Finally, CONCAT('[', 'Q7X', ']') wraps it in brackets: [Q7X]. That confirms C is correct. Now let's trace where the other choices go wrong. A ([ Q7X]) suggests TRIM was never applied — the student extracted ' Q7X' correctly but forgot (or ignored) that TRIM removes the leading space. B ([Q7X9]) is the trap of thinking SUBSTRING starts at position 1 instead of position 2, which would extract ' Q7X'... actually that still doesn't yield Q7X9. This choice tempts students who miscount and think they're grabbing characters 2–5 as Q7X9 — they may have mentally skipped the first space entirely and treated Q as position 1. D ([7X9]) results from starting at position 3 instead of position 2, a simple off-by-one error when counting into the string. The key study tip: always map out the string with explicit position numbers before applying SUBSTRING. Write 1=' ', 2=' ', 3='Q', 4='7'... and mark your start and end. This prevents the off-by-one errors that make distractors A, B, and D so convincing.

Question 6

Assume the dialect supports the standard form TRIM(LEADING '0' FROM value) and one-based substring positions. The column account_code contains '00012030'. What does this expression return?

SUBSTRING(TRIM(LEADING '0' FROM account_code), 2, 3)

  1. 120
  2. 203 (correct answer)
  3. 030
  4. 012
Explanation: When SQL expressions nest multiple string functions, always evaluate from the inside out — the inner function's result becomes the input to the outer function. Start with TRIM(LEADING '0' FROM account_code). Applied to '00012030', this strips all leading zeros, giving you '12030'. That five-character string is now what SUBSTRING works with. Now apply SUBSTRING('12030', 2, 3). Using one-based positioning, position 1 is '1', position 2 is '2', and extracting 3 characters from position 2 gives you '203' — confirming B is correct. Here's why the other choices represent common mistakes. A) 120 is what you'd get if you started the substring at position 1 instead of position 2 — an off-by-one error on the starting index. C) 030 would result if you forgot to apply the TRIM at all, leaving the leading zeros intact: SUBSTRING('00012030', 2, 3) pulls from the second character of the original string, yielding '001' — not even '030', so this choice might tempt students who partially misapply both functions simultaneously. D) 012 is what you'd get if you mistakenly applied the substring before trimming, taking SUBSTRING('00012030', 2, 3) = '001'... actually '001' doesn't match either, suggesting D targets students who confuse the trim's effect entirely and guess based on recognizable digit sequences in the original value. As a strategy, always annotate nested string functions step by step on scratch paper, writing the intermediate string result before moving to the outer function. One skipped step is all it takes to land on a wrong answer.

Question 7

Assume SUBSTRING(value, start, length) uses one-based positions. The column code contains the value ' AB-739 '. What does the following expression return?

CONCAT(SUBSTRING(TRIM(code), 1, 2), SUBSTRING(TRIM(code), 4, 3))

  1. AB-739
  2. AB739 (correct answer)
  3. AB-73
  4. A-739
Explanation: When you see nested string functions like this, your strategy should be to work from the inside out — resolve the innermost functions first, then build outward. Start with TRIM(code). The original value ' AB-739 ' has leading and trailing spaces, so TRIM strips them, leaving 'AB-739'. This trimmed result is what both SUBSTRING calls operate on. Now evaluate each piece. SUBSTRING('AB-739', 1, 2) starts at position 1 and grabs 2 characters: 'AB'. Then SUBSTRING('AB-739', 4, 3) starts at position 4 and grabs 3 characters. Counting through 'AB-739' — position 1 is A, 2 is B, 3 is -, 4 is 7 — so positions 4–6 give you '739'. Finally, CONCAT('AB', '739') joins them: 'AB739', confirming B is correct. As for the wrong answers: A (AB-739) would require the full trimmed string to be returned, but CONCAT of those two specific substrings skips the hyphen entirely. C (AB-73) is a tempting trap if you mistakenly include the hyphen in your character count for the first substring or think SUBSTRING('AB-739', 4, 3) only returns two characters. D (A-739) would result if you treated positions as zero-based (starting at 0), making SUBSTRING(..., 1, 2) return only 'A'. The key study tip: always apply TRIM mentally before counting positions — and remember that SQL's SUBSTRING is one-based, unlike zero-based indexing in many programming languages.

Question 8

A column member_id always contains eight characters in the form represented by AB123456. Assume one-based positions. Which expression transforms this value into AB-3456?

  1. CONCAT(SUBSTRING(member_id, 1, 2), '-', SUBSTRING(member_id, 4, 4))
  2. CONCAT(SUBSTRING(member_id, 2, 2), '-', SUBSTRING(member_id, 5, 4))
  3. CONCAT(SUBSTRING(member_id, 1, 3), '-', SUBSTRING(member_id, 5, 3))
  4. CONCAT(SUBSTRING(member_id, 1, 2), '-', SUBSTRING(member_id, 5, 4)) (correct answer)
Explanation: When working with string manipulation in SQL, the two functions you need to master are SUBSTRING(string, start, length) and CONCAT(). The key is remembering that SUBSTRING takes a starting position and a length — not a start and end position. Map out your source string character by character before choosing an answer. For AB123456, the positions are: A=1, B=2, 1=3, 2=4, 3=5, 4=6, 5=7, 6=8. Your target is AB-3456. Breaking that down: you need AB (characters 1–2), then a literal hyphen, then 3456 (characters 5–8). That means SUBSTRING(member_id, 1, 2) captures AB, and SUBSTRING(member_id, 5, 4) captures 3456 — exactly what option D provides. D is correct. Option A uses SUBSTRING(member_id, 4, 4), which starts at position 4 (the character 2) and returns 2345, not 3456. It's off by one position in the second substring. Option B shifts the first substring to start at position 2 with length 2, returning B1 instead of AB, and also starts the second part at position 5 — so the prefix is already wrong. Option C takes three characters from the start (AB1) instead of two, producing AB1- instead of AB-, and then captures only three characters from position 5, giving 345 instead of 3456. A reliable strategy: always write out the indexed characters explicitly before evaluating each option. Errors in these questions almost always come from being off by one in either the start position or the length — two separate places to make a mistake.

Question 9

For one row, first_name is ' Ana ' and last_name is ' Lee '. Which expression reliably returns exactly Lee, Ana, with no unwanted spaces?

  1. CONCAT(TRIM(last_name), ', ', TRIM(first_name)) (correct answer)
  2. CONCAT(TRIM(first_name), ', ', TRIM(last_name))
  3. CONCAT(TRIM(last_name), ',', TRIM(first_name))
  4. TRIM(CONCAT(last_name, ', ', first_name))
Explanation: When formatting names, two things must go right simultaneously: the order of the parts and the placement of whitespace removal. With first_name = ' Ana ' and last_name = ' Lee ', your goal is Lee, Ana — last name first, followed by a comma-space, then first name, all without stray spaces. TRIM() removes leading and trailing spaces from a single string. The key insight is that you need to TRIM() each name individually before concatenating them. If you trim after concatenating, the outer spaces are gone, but the internal spaces between the names remain — giving you something like Lee , Ana rather than Lee, Ana. A applies TRIM() to both last_name and first_name separately, then concatenates in the correct order: Lee + , + Ana = Lee, Ana. This is correct. B has the right trimming strategy but reverses the order — it puts first_name before last_name, producing Ana, Lee instead of Lee, Ana. C trims correctly and uses the right order, but the separator is ',' (no space after the comma), which produces Lee,Ana — missing the required space. D trims the result of the full CONCAT, not the individual fields. Since last_name and first_name both have internal surrounding spaces, the concatenated string becomes Lee , Ana — trimming only removes the outermost spaces, leaving Lee , Ana with unwanted interior spaces. A handy rule of thumb: trim individual pieces before joining them, not the joined result afterward. This prevents interior whitespace from surviving into your final output.

Question 10

A column invoice_code contains values in the fixed format represented by INV-2026-045. Assume one-based positions. Which expression returns 2026/045 for this value?

  1. CONCAT(SUBSTRING(invoice_code, 4, 4), '/', SUBSTRING(invoice_code, 9, 3))
  2. CONCAT(SUBSTRING(invoice_code, 5, 4), '/', SUBSTRING(invoice_code, 9, 3))
  3. CONCAT(SUBSTRING(invoice_code, 5, 5), '/', SUBSTRING(invoice_code, 10, 3))
  4. CONCAT(SUBSTRING(invoice_code, 5, 4), '/', SUBSTRING(invoice_code, 10, 3)) (correct answer)
Explanation: When working with string functions like SUBSTRING, you need to think carefully about two things: the starting position and the length of the substring you want to extract. In SQL, SUBSTRING(string, start, length) uses one-based indexing, meaning the first character is at position 1. Let's map out INV-2026-045 character by character:
Position123456789101112
CharacterINV-2026-045
To extract 2026, you start at position 5 and take 4 characters. To extract 045, you start at position 10 and take 3 characters. Option D does exactly this: SUBSTRING(invoice_code, 5, 4) returns 2026, and SUBSTRING(invoice_code, 10, 3) returns 045. The CONCAT then joins them with /, producing 2026/045. D is correct. Option A starts at position 4 for the year, which lands on the - separator, returning -202 instead of 2026. Option B correctly extracts the year with (5, 4) but starts the suffix at position 9, which is the - separator, returning -04 instead of 045. Option C starts at position 5 but requests a length of 5 for the year portion, pulling 2026- instead of just 2026. A reliable strategy: before writing or evaluating SUBSTRING calls, physically number each character in the sample string. Off-by-one errors are the most common trap with string functions, and this quick mapping eliminates them entirely.