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.