SUBSTRING extraction in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Extracting a substring from a string

Given a table Employees with a column FullName containing full names, which SQL query extracts the first 4 characters of each name?

SQL

SELECT FullName FROM Employees;

A SELECT SUBSTRING(FullName, 0, 4) FROM Employees;

B SELECT SUBSTRING(FullName, 1, 4) FROM Employees;

C SELECT SUBSTRING(FullName, 1, 5) FROM Employees;

D SELECT SUBSTR(FullName, 2, 4) FROM Employees;

💡 Hint

Remember that SQL substring positions start at 1, not 0.

In SQL, SUBSTRING(string, start, length) extracts a substring starting at position 'start' (1-based) for 'length' characters. Option B correctly extracts 4 characters starting at position 1.

❓ query_result

intermediate

Extracting domain from email addresses

Given a table Users with a column Email containing email addresses, which query extracts the domain part (after '@') correctly?

SQL

SELECT Email FROM Users;

A SELECT SUBSTRING(Email, CHARINDEX('@', Email), LEN(Email)) FROM Users;

B SELECT SUBSTRING(Email, 1, CHARINDEX('@', Email) - 1) FROM Users;

C SELECT SUBSTRING(Email, CHARINDEX('@', Email) + 2, LEN(Email)) FROM Users;

D SELECT SUBSTRING(Email, CHARINDEX('@', Email) + 1, LEN(Email)) FROM Users;

💡 Hint

The domain starts right after the '@' character.

Option D starts extracting from one position after '@' to the end of the string, correctly capturing the domain. Option D extracts the username part, C includes '@' in the result, and D skips one extra character.

📝 Syntax

advanced

Identifying syntax error in SUBSTRING usage

Which of the following SQL queries will cause a syntax error?

A SELECT SUBSTRING(Name FROM 2 FOR 3) FROM Customers;

B SELECT SUBSTRING(Name, 2, 3) FROM Customers;

C SELECT SUBSTRING(Name, 2, 3, 4) FROM Customers;

D SELECT SUBSTRING(Name, 2) FROM Customers;

💡 Hint

Check the number of arguments SUBSTRING accepts in standard SQL.

Standard SQL SUBSTRING accepts either two or three arguments. Option C has four arguments, causing a syntax error. Option C uses the SQL standard syntax with FROM and FOR keywords, which is valid in some SQL dialects.

❓ optimization

advanced

Optimizing substring extraction for performance

You want to extract the first 3 characters of a column Code from a large table Products. Which query is generally more efficient in SQL Server?

A SELECT LEFT(Code, 3) FROM Products;

B SELECT SUBSTRING(Code, 1, 3) FROM Products;

C SELECT RIGHT(Code, LEN(Code) - 3) FROM Products;

D SELECT SUBSTRING(Code, 0, 3) FROM Products;

💡 Hint

Consider built-in functions optimized for common tasks.

LEFT is optimized for extracting characters from the start of a string and is generally faster than SUBSTRING for this purpose. Option A works but may be slightly less efficient. Option A extracts the wrong part, and D is invalid due to 0 start position.

🧠 Conceptual

expert

Understanding SUBSTRING behavior with out-of-range positions

What is the result of this query if the Name column contains 'Amy' (3 characters)? SELECT SUBSTRING(Name, 5, 3) FROM People;

SQL

SELECT SUBSTRING(Name, 5, 3) FROM People WHERE Name = 'Amy';

A An empty string ('')

B NULL

C A syntax error

D The entire string 'Amy'

💡 Hint

What happens if the start position is beyond the string length?

When the start position is beyond the string length, SUBSTRING returns an empty string, not NULL or an error.

Practice

1. What does the SQL function SUBSTRING('Hello World', 7, 5) return?

easy

A. 'Hello W'

B. 'Hello'

C. 'World!'

D. 'World'

Solution

1. Step 1: Understand SUBSTRING parameters

   The function takes the string 'Hello World', starts at position 7, and extracts 5 characters.

2. Step 2: Extract substring from position 7

   Counting characters: H(1) e(2) l(3) l(4) o(5) space(6) W(7) o(8) r(9) l(10) d(11). Starting at 7, 5 characters are 'World'.

3. Final Answer:

   'World' -> Option D

4. Quick Check:

   SUBSTRING('Hello World',7,5) = 'World' [OK]

Hint: Count characters starting at 1, then extract length [OK]

Common Mistakes:

• Starting count at 0 instead of 1
• Extracting fewer or more characters than length
• Including spaces incorrectly

2. Which of the following is the correct syntax to extract 3 characters starting from position 2 in SQL?

easy

A. SUBSTRING('Example', 3)

B. SUBSTRING('Example', 3, 2)

C. SUBSTRING('Example', 2, 3)

D. SUBSTRING('Example', 2)

Solution

1. Step 1: Recall SUBSTRING syntax

   SUBSTRING(string, start_position, length) extracts length characters from start_position.

2. Step 2: Match parameters to requirement

   We want 3 characters starting at position 2, so SUBSTRING('Example', 2, 3) is correct.

3. Final Answer:

   SUBSTRING('Example', 2, 3) -> Option C

4. Quick Check:

   Syntax = SUBSTRING(string, start, length) [OK]

Hint: Syntax is SUBSTRING(string, start, length) [OK]

Common Mistakes:

• Swapping start and length parameters
• Omitting length parameter
• Using zero-based start position

3. Given the table Users with a column username containing 'john_doe123', what is the result of SELECT SUBSTRING(username, 6, 4) FROM Users;?

medium

A. 'doe1'

B. 'doe_'

C. '_doe'

D. 'doe12'

Solution

1. Step 1: Identify substring start and length

   Start at position 6 and extract 4 characters from 'john_doe123'.

2. Step 2: Count characters and extract

   Characters: j(1) o(2) h(3) n(4) _(5) d(6) o(7) e(8) 1(9) 2(10) 3(11). Starting at 6: d(6), o(7), e(8), 1(9) -> 'doe1'.

3. Final Answer:

   'doe1' -> Option A

4. Quick Check:

   SUBSTRING('john_doe123',6,4) = 'doe1' [OK]

Hint: Count characters carefully starting at 1 [OK]

Common Mistakes:

• Starting count at 0
• Extracting wrong number of characters
• Confusing underscore with letter

4. Identify the error in the SQL query: SELECT SUBSTRING('Database', 0, 4);

medium

A. Start position cannot be zero

B. Length cannot be less than 5

C. String must be in double quotes

D. SUBSTRING requires only two parameters

Solution

1. Step 1: Check SUBSTRING start position rules

   In SQL, string positions start at 1, not 0.

2. Step 2: Analyze given query

   Start position is 0, which is invalid and causes an error.

3. Final Answer:

   Start position cannot be zero -> Option A

4. Quick Check:

   Positions start at 1, zero is invalid [OK]

Hint: Start counting at 1, never zero [OK]

Common Mistakes:

• Using zero as start position
• Confusing string quotes
• Wrong number of parameters

5. You have a column product_code with values like 'AB12345XYZ'. Write a SQL query to extract the numeric part (5 digits) starting from position 3. Which query correctly extracts '12345'?

hard

A. SELECT SUBSTRING(product_code, 4, 5) FROM products;

B. SELECT SUBSTRING(product_code, 3, 5) FROM products;

C. SELECT SUBSTRING(product_code, 3, 4) FROM products;

D. SELECT SUBSTRING(product_code, 2, 5) FROM products;

Solution

1. Step 1: Understand the string structure

   'AB12345XYZ' has 'A'(1), 'B'(2), '1'(3), '2'(4), '3'(5), '4'(6), '5'(7), 'X'(8), 'Y'(9), 'Z'(10).

2. Step 2: Extract 5 digits starting at position 3

   Starting at 3, extract 5 characters: '1'(3), '2'(4), '3'(5), '4'(6), '5'(7) -> '12345'.

3. Final Answer:

   SELECT SUBSTRING(product_code, 3, 5) FROM products; -> Option B

4. Quick Check:

   Start=3, length=5 extracts '12345' [OK]

Hint: Count start position carefully, length matches digits needed [OK]

Common Mistakes:

• Starting extraction at wrong position
• Extracting fewer or more characters
• Confusing character positions