Chapter 15 Intermediate 55 Questions

Practice Questions — String Functions in SQL

← Back to Notes

17 Easy

11 Medium

4 Hard

Topic-Specific Questions

Question 1

Easy

What is the output?

SELECT CONCAT('Hello', ' ', 'World');

CONCAT joins arguments.

Hello World

Question 2

Easy

What is the output?

SELECT CONCAT_WS('-', '2026', '04', '16');

CONCAT With Separator.

2026-04-16

Question 3

Easy

What is the output?

SELECT CONCAT('Hello', NULL, 'World');

NULL in CONCAT.

NULL

Question 4

Easy

What is the output?

SELECT CONCAT_WS('-', '2026', NULL, '16');

CONCAT_WS and NULL.

2026-16

Question 5

Easy

What is the output?

SELECT UPPER('Bengaluru'), LOWER('MUMBAI');

Case conversion.

BENGALURU, mumbai

Question 6

Easy

What is the output?

SELECT LENGTH('Hello'), CHAR_LENGTH('Hello');

ASCII: bytes == chars.

5, 5

Question 7

Easy

What is the output?

SELECT LEFT('Bengaluru', 4), RIGHT('Bengaluru', 4);

LEFT prefix, RIGHT suffix.

Beng, luru

Question 8

Easy

What is the output?

SELECT TRIM('  Aarav Kumar  '), CHAR_LENGTH(TRIM('  Aarav Kumar  '));

TRIM removes whitespace from both ends.

Aarav Kumar, 11

Question 9

Easy

What is the output?

SELECT REPLACE('+91-98765-43210', '-', '');

REPLACE every dash with empty.

+919876543210

Question 10

Easy

What is the output?

SELECT LOCATE('@', 'aarav@mail.com');

Position is 1-based.

6

Question 11

Easy

What is the output?

SELECT SUBSTRING('Bengaluru', 4, 3);

Start 4, length 3.

gal

Question 12

Easy

What is the output?

SELECT LPAD('42', 5, '0'), RPAD('abc', 6, '.');

Padding to fixed length.

00042, abc...

Question 13

Medium

What is the output?

SELECT SUBSTRING('aarav@mail.com', LOCATE('@', 'aarav@mail.com') + 1);

Extract domain.

mail.com

Question 14

Medium

What is the output?

SELECT TRIM(LEADING '0' FROM '00012345');

LEADING removes from the left only.

12345

Question 15

Medium

What is the output?

SELECT REVERSE('hello');

Reverse the string.

olleh

Question 16

Medium

What is the output?

SELECT REPEAT('ab', 3);

Multiply the string.

ababab

Question 17

Medium

What is the output?

SELECT FORMAT(1234567.89, 2);

Thousands-separated formatting.

1,234,567.89

Question 18

Medium

What is the output?

SELECT SUBSTR('Bengaluru', -4);

Negative start = from end.

luru

Question 19

Hard

What is the output?

SELECT LENGTH('नमस्ते'), CHAR_LENGTH('नमस्ते');

Hindi uses multi-byte UTF-8.

18, 6

Question 20

Hard

What is the output of this clean query on ' Aarav Kumar '?

SELECT TRIM(REPLACE('  Aarav  Kumar  ', '  ', ' '));

REPLACE processes once, then TRIM.

Aarav Kumar

Mixed & Application Questions

Question 1

Easy

Which function joins strings safely when any argument might be NULL?

CONCAT vs CONCAT_WS.

CONCAT_WS (skips NULL arguments). CONCAT returns NULL if any argument is NULL.

Question 2

Easy

Is MySQL's REPLACE case-sensitive?

Exact matching.

Yes. REPLACE does case-sensitive substring replacement. For case-insensitive, use REGEXP_REPLACE with the 'i' flag in MySQL 8.0+, or normalize case first.

Question 3

Easy

What does LENGTH('हi') return in MySQL with UTF-8 encoding?

One multi-byte + one ASCII.

4 (3 bytes for ह + 1 byte for i)

Question 4

Easy

What is the difference between SUBSTRING(str, 1, 5) and LEFT(str, 5)?

They are equivalent.

They are equivalent. LEFT is more readable for prefixes.

Question 5

Easy

Write a query to return the last 4 characters of a phone number stored in phone column.

RIGHT function.

SELECT RIGHT(phone, 4) FROM customers;

Question 6

Medium

Write a query to extract the email domain (part after @) from every customer.

SUBSTRING + LOCATE.

SELECT email, SUBSTRING(email, LOCATE('@', email) + 1) AS domain
FROM customers;

Question 7

Medium

Normalize city names so they have proper case: first letter uppercase, rest lowercase.

UPPER + LOWER + SUBSTRING.

SELECT city, CONCAT(UPPER(LEFT(city, 1)), LOWER(SUBSTRING(city, 2))) AS proper_city
FROM customers;

Question 8

Medium

Write a query to remove all non-digit characters from a phone column using REPLACE.

Chain REPLACE for each separator.

SELECT REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(phone, '-',''), ' ',''), '.',''), '+',''), '(','') AS digits
FROM customers;

Question 9

Medium

When should you use CHAR_LENGTH over LENGTH?

Multi-byte characters.

Always use CHAR_LENGTH for user-facing length operations (validation, truncation). Use LENGTH only when you care about byte storage (e.g., checking maximum byte size for a column).

Question 10

Medium

Why is CONCAT_WS often preferred over CONCAT?

NULL safety + cleaner syntax.

CONCAT_WS inserts a separator automatically and skips NULL arguments. CONCAT returns NULL if any argument is NULL and requires manually adding separators between arguments.

Question 11

Hard

Write a query that masks email so only the first 2 characters and the domain show, e.g., 'aa***@mail.com'.

LEFT + REPEAT + SUBSTRING + LOCATE.

SELECT
    email,
    CONCAT(
        LEFT(email, 2),
        REPEAT('*', 3),
        SUBSTRING(email, LOCATE('@', email))
    ) AS masked
FROM customers;

Question 12

Hard

Write a query to count the number of spaces in each full_name.

LENGTH difference after REPLACE.

SELECT
    full_name,
    CHAR_LENGTH(full_name) - CHAR_LENGTH(REPLACE(full_name, ' ', '')) AS space_count
FROM customers;

Multiple Choice Questions

MCQ 1

What does CONCAT return if one argument is NULL?

A. Empty string
B. The non-NULL arguments joined
C. NULL
D. Error

Answer: C
C is correct. CONCAT returns NULL if any argument is NULL. Use CONCAT_WS or COALESCE to handle NULLs safely.

MCQ 2

Which returns the number of characters (not bytes)?

A. LENGTH
B. CHAR_LENGTH
C. SIZEOF
D. BYTE_LENGTH

Answer: B
B is correct. CHAR_LENGTH (aka CHARACTER_LENGTH) counts characters. LENGTH counts bytes.

MCQ 3

SELECT SUBSTRING('Bengaluru', 4, 3); returns:

A. 'Ben'
B. 'gal'
C. 'galu'
D. 'luru'

Answer: B
B is correct. Starting at position 4 ('g'), take 3 characters: 'gal'. MySQL SUBSTRING is 1-indexed.

MCQ 4

Which function removes whitespace from both ends?

A. LTRIM
B. RTRIM
C. TRIM
D. STRIP

Answer: C
C is correct. TRIM removes from both sides by default. LTRIM is left only, RTRIM is right only. STRIP is not a MySQL function.

MCQ 5

SELECT LPAD('5', 4, '0'); returns:

A. '5000'
B. '0005'
C. '5 '
D. ' 5'

Answer: B
B is correct. LPAD pads on the left to length 4 with '0', giving '0005'. Useful for ID formatting.

MCQ 6

Which returns the 1-based position of a substring (or 0 if not found)?

A. INDEX
B. FIND
C. LOCATE
D. SEARCH

Answer: C
C is correct. LOCATE(substr, str) returns the 1-based position. INSTR and POSITION do the same thing with different argument orders.

MCQ 7

Is MySQL's REPLACE case-sensitive?

A. Yes
B. No
C. Depends on collation
D. Only in MySQL 5.x

Answer: A
A is correct. REPLACE does exact case-sensitive matching. For case-insensitive, use REGEXP_REPLACE with the 'i' flag (MySQL 8.0+).

MCQ 8

SELECT CONCAT_WS('-', 'a', NULL, 'c'); returns:

A. 'a-NULL-c'
B. 'a--c'
C. 'a-c'
D. NULL

Answer: C
C is correct. CONCAT_WS skips NULL arguments. The separator is placed only between actual values, yielding 'a-c'.

MCQ 9

What is the output of SELECT LENGTH('नमस्ते'), CHAR_LENGTH('नमस्ते'); in UTF-8?

A. 6, 6
B. 18, 6
C. 6, 18
D. 18, 18

Answer: B
B is correct. Each Devanagari character is 3 bytes in UTF-8. LENGTH = 6 * 3 = 18 bytes. CHAR_LENGTH = 6 characters.

MCQ 10

Which query gets the email domain (part after '@')?

A. SELECT SUBSTRING(email, 1, LOCATE('@', email));
B. SELECT SUBSTRING(email, LOCATE('@', email));
C. SELECT SUBSTRING(email, LOCATE('@', email) + 1);
D. SELECT RIGHT(email, LOCATE('@', email));

Answer: C
C is correct. Start one position after '@', no length = to end. A returns the username part + '@'. B returns '@domain'. D is wrong.

MCQ 11

SELECT TRIM(LEADING '0' FROM '000042'); returns:

A. '42'
B. '000042'
C. '042'
D. '42000'

Answer: A
A is correct. TRIM LEADING removes the specified character only from the left. All leading zeros stripped.

MCQ 12

Count the occurrences of 'a' in 'banana' using only string functions.

A. LOCATE('a', 'banana')
B. CHAR_LENGTH('banana') - CHAR_LENGTH(REPLACE('banana', 'a', ''))
C. LENGTH('banana') / 3
D. SUBSTRING('banana', 'a')

Answer: B
B is correct. Subtract the length after removing the target character from the original length. Works for counting any character.

MCQ 13

Which is the safest way to join first, middle, and last names where middle can be NULL?

A. CONCAT(first, ' ', middle, ' ', last)
B. first + middle + last
C. CONCAT_WS(' ', first, middle, last)
D. first | middle | last

Answer: C
C is correct. CONCAT_WS skips NULL middle name. A returns NULL if middle is NULL. B and D are invalid syntax for string concatenation in MySQL.

MCQ 14

How do you format an integer id as a 6-digit zero-padded code?

A. CONCAT('0', id)
B. LPAD(id, 6, '0')
C. RPAD(id, 6, '0')
D. FORMAT(id, 6)

Answer: B
B is correct. LPAD pads on the left to reach 6 characters with '0'. RPAD would pad on the right. FORMAT adds thousands separators and decimals, not zero padding.

MCQ 15

Which approach correctly finds duplicate emails ignoring case?

A. SELECT email FROM users GROUP BY email HAVING COUNT(*) > 1
B. SELECT LOWER(email) FROM users GROUP BY LOWER(email) HAVING COUNT(*) > 1
C. SELECT email FROM users WHERE UPPER(email) = LOWER(email)
D. SELECT DISTINCT email FROM users

Answer: B
B is correct. Normalize by LOWER before grouping. A would miss duplicates differing in case. C is a nonsense condition. D just returns unique cases.

Coding Challenges

Challenge 1: Clean Up Name Column

Easy

Given the customers table, return id and full_name trimmed of leading/trailing spaces.

SELECT id, TRIM(full_name) AS clean_name
FROM customers;

Challenge 2: Normalize Email to Lowercase

Easy

Return id and email converted to lowercase for all customers.

SELECT id, LOWER(email) AS normalized_email
FROM customers;

Challenge 3: Extract Email Username and Domain

Medium

For each customer, return the email, the username (before '@'), and the domain (after '@').

SELECT
    email,
    SUBSTRING(email, 1, LOCATE('@', email) - 1) AS username,
    SUBSTRING(email, LOCATE('@', email) + 1)   AS domain
FROM customers;

Challenge 4: Digits-Only Phone Numbers

Medium

Strip all separators (-, space, ., +) from phone numbers and return just the digits.

SELECT
    phone AS original,
    REPLACE(REPLACE(REPLACE(REPLACE(phone, '-', ''), ' ', ''), '.', ''), '+', '') AS digits_only
FROM customers;

Challenge 5: Proper Case City Names

Medium

Capitalize the first letter of each city name, rest lowercase, e.g., 'bengaluru' -> 'Bengaluru'.

SELECT
    city AS original,
    CONCAT(UPPER(LEFT(city, 1)), LOWER(SUBSTRING(city, 2))) AS proper_city
FROM customers;

Challenge 6: Count Spaces in Name

Hard

Return the number of space characters in each full_name.

SELECT
    full_name,
    CHAR_LENGTH(full_name) - CHAR_LENGTH(REPLACE(full_name, ' ', '')) AS space_count
FROM customers;

Challenge 7: Mask Phone Number (Last 4 Digits Only)

Hard

Mask all but the last 4 digits of a phone number with 'X'. Input format may vary.

SELECT
    phone,
    CONCAT(
        REPEAT('X', CHAR_LENGTH(phone) - 4),
        RIGHT(phone, 4)
    ) AS masked
FROM customers;

Challenge 8: Extract First Name (Before First Space)

Hard

Given full_name, return just the first name (portion before first space). Handle names without spaces by returning the whole name.

SELECT
    full_name,
    TRIM(
        CASE
            WHEN LOCATE(' ', TRIM(full_name)) = 0
                THEN TRIM(full_name)
            ELSE SUBSTRING(TRIM(full_name), 1, LOCATE(' ', TRIM(full_name)) - 1)
        END
    ) AS first_name
FROM customers;

Need to Review the Concepts?

Go back to the detailed notes for this chapter.

Read Chapter Notes

Want to master SQL and databases with a mentor?

Explore our MySQL Masterclass