SQL String Functions – SUBSTR, CONCAT, UPPER, LOWER, TRIM, REPLACE: 20 Real Interview Questions

SQL string functions fundamentals are essential for data cleaning, transformation, and validation. String functions manipulate text data: extracting substrings, combining fields, changing case, removing whitespace, and finding/replacing patterns.

Understanding SQL String Functions Fundamentals

SQL string functions fundamentals cover text manipulation operations that appear in nearly every analytics query. Unlike numeric or date functions, string operations are heavily used for data quality work—cleaning messy customer names, parsing addresses, validating formats, and constructing display values.

SUBSTR (or SUBSTRING): Extracting Parts of Text

SQL string functions fundamentals start with SUBSTR (MySQL, Oracle) or SUBSTRING (SQL Server, PostgreSQL). SUBSTR(column, start_position, length) extracts a portion of a string. Positions are 1-indexed (not 0). SUBSTR(phone, 1, 3) extracts first 3 characters (area code). SUBSTR(email, POSITION(@) + 1) extracts domain after @.

CONCAT: Combining Strings Together

SQL string functions fundamentals include CONCAT(str1, str2, str3, …) which combines multiple strings. Easier than || operator (depends on database). CONCAT(Hello , World) = Hello World. Null-safe CONCAT_WS(separator, str1, str2) skips NULLs and adds separator between non-null values.

UPPER and LOWER: Case Conversion

SQL string functions fundamentals include UPPER() converts to uppercase, LOWER() converts to lowercase. Used for case-insensitive comparisons: WHERE UPPER(email) = UPPER(@search). Critical for data quality—inconsistent case ruins joins and deduplication.

TRIM, LTRIM, RTRIM: Removing Whitespace

SQL string functions fundamentals teach TRIM() removes leading and trailing spaces. LTRIM() removes left, RTRIM() removes right. Essential for data quality—leading/trailing spaces cause non-matches in joins. TRIM(name) = TRIM(search_name) is common for fuzzy matching.

REPLACE: Find and Replace

SQL string functions fundamentals include REPLACE(string, find, replace). REPLACE(phone, -, ) removes hyphens. REPLACE(email, @old-domain, @new-domain) updates domains in bulk. Be careful: REPLACE is case-sensitive in some databases (WHERE LOWER(column) = LOWER(search)).

5 Complete Interview Questions with Solutions

Question 1: SUBSTR for Data Extraction

Customer phone: 1-555-123-4567. Extract area code (555). Use SUBSTR with POSITION or hardcoded indices.

SELECT phone,
       SUBSTR(phone, 3, 3) AS area_code,
       SUBSTR(phone, POSITION(- IN phone) + 1, 3) AS area_code_dynamic
FROM customers;

Explanation: SUBSTR(phone, 3, 3) extracts 3 characters starting at position 3. Hardcoded but fragile if format changes. Dynamic approach finds first − and extracts next 3 characters. SQL string functions fundamentals require both approaches.

Google – Senior Data AnalystAmazon – Analytics EngineerMeta – BI Engineer

Question 2: CONCAT and CONCAT_WS

Create full_address: combine address, city, state (some NULL). Use CONCAT vs CONCAT_WS(, , …). Handle NULL gracefully.

SELECT CONCAT(address, , , city, , , state) AS concat_result,
       CONCAT_WS(, , address, city, state) AS concat_ws_result
FROM addresses;

Explanation: CONCAT with NULL = NULL (entire result NULL if any part is NULL). CONCAT_WS skips NULLs and adds separator only between non-null values. CONCAT_WS is safer for optional fields. SQL string functions fundamentals require understanding this NULL behavior.

Microsoft – Analytics EngineerAirbnb – Senior Data AnalystFlipkart – Data Analyst

Question 3: UPPER/LOWER for Case-Insensitive Matching

Customer data has inconsistent case: john@email.com, JANE@EMAIL.COM. Deduplicate using case-insensitive match.

SELECT LOWER(email) AS email_normalized, COUNT(*) AS count
FROM customers
GROUP BY LOWER(email)
HAVING COUNT(*) > 1;

Explanation: LOWER() normalizes all emails to lowercase for grouping. Reveals duplicates hidden by case differences. SQL string functions fundamentals: always normalize before joins/deduplication. Storing normalized versions in database prevents repeated conversion.

LinkedIn – Senior Analytics EngineerOYO – BI Engineer

Question 4: TRIM for Data Quality

Product names have leading/trailing spaces: Widget (5 chars with spaces). COUNT(DISTINCT name) vs COUNT(DISTINCT TRIM(name))?

SELECT COUNT(DISTINCT name) AS with_spaces,
       COUNT(DISTINCT TRIM(name)) AS trimmed
FROM products;

Explanation: TRIM reveals duplicates masked by whitespace. Widget and Widget are different strings (count = 2 with spaces, 1 trimmed). LTRIM removes left only, RTRIM removes right only. Essential for deduplication before joins.

Amazon – Senior BI EngineerGoogle – Analytics Engineer

Question 5: REPLACE for Bulk Updates

Update all emails: old domain @oldco.com → @newco.com. Use REPLACE without updating table.

SELECT email,
       REPLACE(email, @oldco.com, @newco.com) AS new_email,
       CASE WHEN email LIKE %oldco.com% THEN Migrate
            ELSE No change END AS action
FROM users;

Explanation: REPLACE(email, find, replace) creates new_email without changing original. Case-sensitive in most databases. Verify with LIKE before bulk update. SQL string functions fundamentals: always preview changes before committing.

Stripe – Data AnalystMicrosoft – Senior Data Analyst

15 Practice Questions (Test Yourself)

Question 6: When you use SUBSTR with negative positions or zero index values like SUBSTR(hello, 0, 2), how do different databases like MySQL and PostgreSQL handle these edge cases differently?

Google – Data AnalystAmazon – Senior Analytics Engineer

Question 7: If you concatenate five strings together using CONCAT and one of the middle values contains a NULL value, what would be the final result of the entire concatenation operation?

Meta – BI EngineerFlipkart – Analytics Engineer

Question 8: When performing UPPER() or LOWER() operations on a table with 100 million rows, each conversion taking 1.5 seconds, would these functions be able to effectively use database indexes for query optimization?

Airbnb – Senior Data AnalystLinkedIn – Data AnalystOYO – Senior BI Engineer

Question 9: The TRIM function removes only leading and trailing spaces from text like hello world, so what would happen if you wanted to remove all internal spaces within the string as well?

Google – Senior BI EngineerAmazon – Analytics Engineer

Question 10: If you want to validate an email address using string functions, which combination of SUBSTR, POSITION, and LIKE would be more effective for checking format correctness?

Microsoft – Analytics EngineerStripe – Data AnalystMeta – Senior Analytics Engineer

Question 11: To extract the domain portion of an email address like user@example.com where everything appears after the @ symbol, how would you combine SUBSTR and POSITION functions together?

Amazon – Data AnalystGoogle – Senior Analytics Engineer

Question 12: When parsing a full name like John Smith into separate first and last name columns, how would you use SUBSTR and POSITION to find the space character separator reliably?

Flipkart – Senior Data AnalystLinkedIn – Analytics EngineerAirbnb – BI Engineer

Question 13: Between using CONCAT_WS and the pipe operator (||) for string concatenation, how do these approaches differ in their handling of NULL values and which databases support each syntax?

OYO – Analytics EngineerGoogle – BI Engineer

Question 14: When using REPLACE to change email domains from @Gmail.com to @gmail.com, why might the replacement fail if you dont account for case sensitivity in your search pattern?

Amazon – Senior BI EngineerMeta – Data AnalystStripe – Analytics Engineer

Question 15: If you chain multiple string functions together like UPPER(TRIM(SUBSTR(column, start, length))), in what order do these functions execute and does nesting multiple functions impact query performance?

Microsoft – Analytics EngineerFlipkart – BI Engineer

Question 16: When validating email addresses, how would you decide between using LIKE with pattern matching versus using SUBSTR and POSITION to manually check for the presence of required characters?

Google – Data AnalystLinkedIn – Senior Analytics EngineerAmazon – BI Engineer

Question 17: Would it be beneficial to create a functional index on LOWER(email) for queries that frequently perform case-insensitive searches on large email columns, compared to using UPPER or LOWER in every WHERE clause?

Airbnb – Senior Data AnalystOYO – Analytics Engineer

Question 18: To extract each octet from an IP address like 192.168.1.1, how would you use SUBSTR in combination with POSITION to locate each dot separator reliably?

Meta – Senior Analytics EngineerGoogle – BI EngineerMicrosoft – Data Analyst

Question 19: When comparing string lengths using LENGTH() versus CHAR_LENGTH() on UTF-8 text containing accented characters like héllo, why would these two functions return different values for the same string?

Stripe – Data AnalystFlipkart – Senior Analytics Engineer

Question 20: When you need to update a column using REPLACE and display both the original and replaced values in a query, how would you structure the SELECT statement to show the impact of changes before committing them to the database?

Amazon – Analytics EngineerLinkedIn – BI EngineerGoogle – Senior BI Engineer

Ready to Master SQL String Functions & All Analytics Concepts?

These 20 practice questions are just the tip of the iceberg. Get our comprehensive Data Analytics: Ace All The Interview Concepts course.