🎓 Velnov

🗄️ SQL · Topic 9 of 20

String Functions — SQL Questions with Answers

30 practice questions on String Functions, each with the correct answer and a clear explanation of why it is right. Read through them as revision notes, or take this topic as an interactive quiz to test yourself.

3 easy · 17 medium · 10 hard · Part of SQL: Databases from basics to window functions - 20 topics.

Key concepts in this topic

CONCATString joiningLENGTHLENUPPERLOWERTRIMLTRIMRTRIMSUBSTRINGSUBSTRIndexingREPLACEPOSITIONCHARINDEXINSTRLEFTRIGHTREVERSENULLDialectsCONCAT_WSLPADRPAD

Jump to: Easy · Medium · Hard

Easy questions (3)

Q1. easy

Which function joins two or more strings together?

SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM users;
  1. A CONCAT(first, ' ', last)
  2. B JOIN(first, last)
  3. C MERGE(first, last)
  4. D ADD(first, last)

Answer: A — CONCAT(first, ' ', last)

CONCAT joins its arguments into one string and works in most databases. The standard operator || does the same in PostgreSQL, Oracle and SQLite; SQL Server uses +.

CONCATString joining

Q2. easy

What does LENGTH('hello') return?

SELECT LENGTH('hello');   -- 5
  1. A 5
  2. B 4
  3. C 6
  4. D 'hello'

Answer: A — 5

LENGTH (LEN in SQL Server) returns the number of characters. Be careful with trailing spaces and with multi-byte characters, where CHAR_LENGTH may differ from byte length.

LENGTHLEN

Q3. easy

Which function converts text to capital letters?

SELECT UPPER('sql');   -- 'SQL'
  1. A UPPER()
  2. B CAPS()
  3. C TOUPPER()
  4. D BIG()

Answer: A — UPPER()

UPPER() uppercases and LOWER() lowercases. They are essential for case-insensitive comparisons: WHERE LOWER(email) = LOWER(:input).

UPPERLOWER

Medium questions (17)

Q4. medium

What does TRIM(' hello ') return?

SELECT TRIM('  hello  ');   -- 'hello'
  1. A 'hello' - leading and trailing spaces removed
  2. B ' hello '
  3. C 'hello '
  4. D 'h e l l o'

Answer: A — 'hello' - leading and trailing spaces removed

TRIM removes whitespace from both ends. LTRIM removes only the left side and RTRIM only the right. Spaces inside the string are untouched.

TRIMLTRIMRTRIM

Q5. medium

What does SUBSTRING('database', 1, 4) return?

SELECT SUBSTRING('database', 1, 4);   -- 'data'
SELECT SUBSTRING('database', 5, 4);   -- 'base'
  1. A 'data' - 4 characters starting at position 1
  2. B 'base'
  3. C 'atab'
  4. D 'databa'

Answer: A — 'data' - 4 characters starting at position 1

SUBSTRING(string, start, length) extracts a portion. Note SQL positions are usually 1-based, not 0-based like most programming languages.

SUBSTRINGSUBSTR

Q7. medium

What does REPLACE('2026-07-04', '-', '/') return?

SELECT REPLACE(phone, '-', '');   -- strip dashes
  1. A '2026/07/04' - every '-' replaced with '/'
  2. B '20260704'
  3. C Only the first '-' replaced
  4. D An error

Answer: A — '2026/07/04' - every '-' replaced with '/'

REPLACE swaps EVERY occurrence of the search text, not just the first. It is commonly used for reformatting and cleaning data.

REPLACE

Q8. medium

How do you find the position of a substring?

SELECT SUBSTRING(email, 1, POSITION('@' IN email) - 1) AS username;
  1. A POSITION('@' IN email) or CHARINDEX / INSTR depending on the database
  2. B FIND('@', email)
  3. C INDEX(email, '@')
  4. D SEARCH(email, '@')

Answer: A — POSITION('@' IN email) or CHARINDEX / INSTR depending on the database

POSITION is standard SQL; SQL Server uses CHARINDEX('@', email) and MySQL/Oracle use INSTR(email, '@'). All return 1-based positions, or 0 when not found.

POSITIONCHARINDEXINSTR

Q10. medium

What do LEFT('hello', 3) and RIGHT('hello', 2) return?

SELECT LEFT(code, 2) AS prefix, RIGHT(code, 4) AS suffix FROM items;
  1. A 'hel' and 'lo'
  2. B 'llo' and 'he'
  3. C 'hel' and 'll'
  4. D 'lo' and 'hel'

Answer: A — 'hel' and 'lo'

LEFT takes characters from the start, RIGHT from the end. They are shorthand for common SUBSTRING cases. (Oracle lacks them; use SUBSTR instead.)

LEFTRIGHT

Q11. medium

What does REVERSE('SQL') return?

  1. A 'LQS'
  2. B 'SQL'
  3. C 'QLS'
  4. D An error

Answer: A — 'LQS'

REVERSE flips the character order. It is occasionally useful for extracting text after the LAST occurrence of a character (reverse, find, reverse again).

REVERSE

Q13. medium

What does CONCAT_WS(', ', city, state, country) do?

SELECT CONCAT_WS(', ', city, state, country) AS location FROM addresses;
  1. A Joins with a separator AND skips NULL arguments - no doubled separators
  2. B Joins without any separator
  3. C Only joins two strings
  4. D Reverses the string

Answer: A — Joins with a separator AND skips NULL arguments - no doubled separators

CONCAT_WS ('with separator') is ideal for addresses: it puts ', ' between values and gracefully skips missing fields instead of leaving ', , '.

CONCAT_WSNULL

Q14. medium

How do you pad a number to a fixed width, like '007'?

SELECT LPAD(CAST(id AS CHAR), 5, '0') FROM invoices;   -- 00042
  1. A LPAD('7', 3, '0')
  2. B PAD('7', 3)
  3. C FILL('7', '0', 3)
  4. D FORMAT('7', 3)

Answer: A — LPAD('7', 3, '0')

LPAD pads on the left to reach a target length; RPAD pads on the right. Useful for invoice numbers and codes. (SQL Server lacks LPAD - use RIGHT('000' + val, 3).)

LPADRPAD

Q16. medium

What does SUBSTRING('database' FROM 5) return (no length given)?

  1. A 'base' - from position 5 to the end
  2. B 'data'
  3. C 'd'
  4. D An error

Answer: A — 'base' - from position 5 to the end

Omitting the length extracts everything from the start position onward. This is handy when you know where to start but not how long the remainder is.

SUBSTRING

Q17. medium

How do you capitalise the first letter of each word (title case)?

  1. A INITCAP('hello world') in PostgreSQL/Oracle returns 'Hello World'
  2. B UPPER() does it automatically
  3. C TITLE('hello world')
  4. D CAPITALIZE()

Answer: A — INITCAP('hello world') in PostgreSQL/Oracle returns 'Hello World'

INITCAP handles title case in PostgreSQL and Oracle. MySQL and SQL Server lack it, so you build it with SUBSTRING + UPPER or a custom function.

INITCAPDialects

Q19. medium

How do you split a full name into first and last using space position?

SELECT SPLIT_PART(name, ' ', 1) AS first_name;   -- PostgreSQL
  1. A Use POSITION(' ' IN name) with SUBSTRING for each part
  2. B SPLIT(name, ' ')
  3. C name.split(' ')
  4. D EXPLODE(name)

Answer: A — Use POSITION(' ' IN name) with SUBSTRING for each part

Most databases lack a simple SPLIT into columns. You locate the delimiter and slice around it. (PostgreSQL has SPLIT_PART(name, ' ', 1) which is much cleaner.)

SPLIT_PARTPOSITION

Q21. medium

How do you count occurrences of a character in a string?

SELECT LENGTH(tags) - LENGTH(REPLACE(tags, ',', '')) + 1 AS tag_count;
  1. A LENGTH(str) - LENGTH(REPLACE(str, 'x', '')) gives the count of 'x'
  2. B COUNT('x', str)
  3. C OCCURRENCES(str, 'x')
  4. D There is no way

Answer: A — LENGTH(str) - LENGTH(REPLACE(str, 'x', '')) gives the count of 'x'

A clever idiom: remove the character, and the drop in length equals how many were removed. Works in any database with LENGTH and REPLACE.

LENGTHREPLACETricks

Q22. medium

What does the || operator do in PostgreSQL?

  1. A String concatenation - 'Hello' || ' World' gives 'Hello World'
  2. B Logical OR
  3. C Bitwise OR
  4. D Absolute value

Answer: A — String concatenation - 'Hello' || ' World' gives 'Hello World'

In standard SQL, || concatenates strings (it is NOT logical OR - that is the keyword OR). Remember it returns NULL if either side is NULL.

ConcatenationOperators

Q24. medium

What does FORMAT or TO_CHAR help with?

  1. A Formatting numbers and dates as strings - e.g. adding commas or currency symbols
  2. B Only uppercasing
  3. C Splitting strings
  4. D Counting rows

Answer: A — Formatting numbers and dates as strings - e.g. adding commas or currency symbols

TO_CHAR(1234567, '9,999,999') gives '1,234,567' in PostgreSQL/Oracle; SQL Server uses FORMAT(). Ideally do display formatting in the application, not the query.

TO_CHARFORMAT

Q25. medium

How do you remove all spaces from a string?

  1. A REPLACE(str, ' ', '')
  2. B TRIM(str)
  3. C DELETE(str, ' ')
  4. D STRIP(str)

Answer: A — REPLACE(str, ' ', '')

TRIM only removes spaces at the ENDS. REPLACE with an empty string removes them everywhere - useful for normalising phone numbers or codes.

REPLACETRIM

Q27. medium

How do you mask a value like a credit card, showing only the last 4?

  1. A CONCAT('****-****-****-', RIGHT(card, 4))
  2. B HIDE(card)
  3. C MASK(card, 4)
  4. D card - 4

Answer: A — CONCAT('****-****-****-', RIGHT(card, 4))

Combine a literal mask with RIGHT to reveal only the tail. This is a common display transformation - though real card numbers should never be stored in plain text.

RIGHTCONCATSecurity

Q29. medium

How do you compare strings ignoring case portably?

  1. A LOWER(a) = LOWER(b) - normalise both sides
  2. B a == b
  3. C a EQUALS b
  4. D IGNORECASE(a, b)

Answer: A — LOWER(a) = LOWER(b) - normalise both sides

Since collation defaults differ between databases, normalising with LOWER (or UPPER) guarantees consistent case-insensitive comparison - at the cost of possibly bypassing an index.

LOWERCollation

Hard questions (10)

Q6. hard

In SQL, string positions typically start at what number?

  1. A 1 - SQL is 1-indexed, unlike most programming languages
  2. B 0
  3. C -1
  4. D It varies randomly

Answer: A — 1 - SQL is 1-indexed, unlike most programming languages

SUBSTRING('abc', 1, 1) returns 'a'. This 1-based indexing catches out developers used to Python or JavaScript, where the first character is at index 0.

IndexingSUBSTRING

Q9. hard

How would you extract the username (before @) from an email?

  1. A SUBSTRING(email, 1, POSITION('@' IN email) - 1)
  2. B LEFT(email, '@')
  3. C SPLIT(email, '@')
  4. D email BEFORE '@'

Answer: A — SUBSTRING(email, 1, POSITION('@' IN email) - 1)

Find the @ position, then take everything before it. The -1 excludes the @ itself. Combining POSITION with SUBSTRING is a common text-parsing pattern.

SUBSTRINGPOSITION

Q12. hard

What does CONCAT('Hi', NULL, 'there') return in MySQL vs the || operator?

-- safe with ||:
first || COALESCE(' ' || middle, '') || ' ' || last
  1. A CONCAT ignores NULL and returns 'Hithere'; || in standard SQL would return NULL
  2. B Both return NULL
  3. C Both return 'Hithere'
  4. D Both error

Answer: A — CONCAT ignores NULL and returns 'Hithere'; || in standard SQL would return NULL

CONCAT() in MySQL and SQL Server treats NULL as an empty string, but the standard || operator (and Oracle) returns NULL if any operand is NULL. This difference causes real bugs when building names.

CONCATNULLDialects

Q15. hard

Why might WHERE UPPER(name) = 'ANA' be slow on a large table?

  1. A Wrapping the column in a function prevents the index on name from being used
  2. B UPPER is always slow
  3. C It returns wrong results
  4. D It is not slow

Answer: A — Wrapping the column in a function prevents the index on name from being used

The database must compute UPPER for every row. Solutions: a case-insensitive collation, a functional index on UPPER(name), or a generated column storing the uppercased value.

SargabilityIndexesPerformance

Q18. hard

What does TRIM(BOTH 'x' FROM 'xxhelloxx') return?

  1. A 'hello' - TRIM can remove characters other than spaces
  2. B 'xxhelloxx'
  3. C 'helloxx'
  4. D An error

Answer: A — 'hello' - TRIM can remove characters other than spaces

Standard TRIM accepts a character set and a direction (BOTH, LEADING, TRAILING). Not every database supports the full syntax, but it is powerful for cleaning delimited data.

TRIMStandard SQL

Q20. hard

What does SPLIT_PART('a,b,c', ',', 2) return in PostgreSQL?

  1. A 'b' - the 2nd part when split on comma
  2. B 'a'
  3. C 'c'
  4. D 'a,b'

Answer: A — 'b' - the 2nd part when split on comma

SPLIT_PART extracts one field from delimited text by position (1-based). It is the neat way to parse CSV-like values stored in a single column.

SPLIT_PARTParsing

Q23. hard

Which finds rows where a name contains only letters (basic validation)?

WHERE name ~ '^[A-Za-z ]+$';   -- letters and spaces only
  1. A WHERE name ~ '^[A-Za-z]+$' (PostgreSQL regex) or a LIKE/pattern check
  2. B WHERE name = LETTERS
  3. C WHERE ISALPHA(name)
  4. D WHERE name LIKE '%[0-9]%'

Answer: A — WHERE name ~ '^[A-Za-z]+$' (PostgreSQL regex) or a LIKE/pattern check

PostgreSQL's ~ operator applies a regular expression. MySQL uses REGEXP, and SQL Server uses LIKE with character ranges. Regex is the flexible tool for text validation.

RegexValidation

Q26. hard

Why can trailing spaces in CHAR columns cause comparison surprises?

  1. A CHAR pads with spaces, so 'ab' stored in CHAR(5) is really 'ab ' - comparisons may ignore or include the padding depending on rules
  2. B CHAR never pads
  3. C Spaces are always removed
  4. D It never causes issues

Answer: A — CHAR pads with spaces, so 'ab' stored in CHAR(5) is really 'ab ' - comparisons may ignore or include the padding depending on rules

Standard SQL comparison ignores trailing spaces for equality, but LENGTH and LIKE may not. VARCHAR avoids the whole problem by storing exactly what you insert.

CHARTrailing spaces

Q28. hard

What does REGEXP_REPLACE do?

SELECT REGEXP_REPLACE(phone, '[^0-9]', '', 'g') AS digits FROM contacts;
  1. A Replaces text matching a regular expression pattern - more powerful than plain REPLACE
  2. B Only replaces the first character
  3. C Reverses a string
  4. D Counts matches

Answer: A — Replaces text matching a regular expression pattern - more powerful than plain REPLACE

REGEXP_REPLACE(phone, '[^0-9]', '', 'g') strips every non-digit in one call. Available in PostgreSQL, Oracle and MySQL 8+.

REGEXP_REPLACERegex

Q30. hard

Which query finds names with leading or trailing spaces (a data-quality check)?

  1. A WHERE name <> TRIM(name)
  2. B WHERE name LIKE ' %'
  3. C WHERE LENGTH(name) > 0
  4. D WHERE name IS NULL

Answer: A — WHERE name <> TRIM(name)

If a value differs from its trimmed version, it has stray spaces. This one-line check finds messy data that would otherwise break joins and grouping. (Option B only catches leading spaces.)

TRIMData quality

Practice “String Functions” as a quiz