What are String Functions in SQL?
Learn SQL string functions like UPPER, LOWER, TRIM, CONCAT, SUBSTRING and REPLACE to clean, format and search text directly inside your queries.
Expected Interview Answer
String functions in SQL are built-in functions that manipulate and inspect text values, such as changing case, trimming spaces, joining strings, extracting substrings, replacing characters, and measuring length.
Common examples include UPPER/LOWER for case, LENGTH/LEN for size, CONCAT to join, SUBSTRING to slice, TRIM to remove padding, REPLACE to swap text, and POSITION/CHARINDEX to find where a substring occurs. They let you clean, standardize, search, and format text right in the query, which is essential for reporting, data cleaning, and matching values that were entered inconsistently.
- Clean and standardize messy text data
- Search and match on parts of a string
- Format output for readable reports
- Join and split values without application code
- Enable case-insensitive comparisons and trimming
AI Mentor Explanation
String functions are like a scorer tidying player names on the team sheet: they capitalize each name consistently, trim stray spaces before printing the scorecard, and stitch first and last names into one display label. UPPER, TRIM, and CONCAT do that text housekeeping so 'sachin ' and 'Sachin' never look like two different batters.
Step-by-Step Explanation
Step 1
Fix case
Use UPPER() or LOWER() to standardize capitalization for display or case-insensitive matching.
Step 2
Trim padding
Use TRIM() (or LTRIM/RTRIM) to remove leading and trailing spaces from user-entered text.
Step 3
Join values
Use CONCAT() or the || operator to combine columns like first and last name into one string.
Step 4
Slice substrings
Use SUBSTRING(col, start, length) to extract a portion, often with POSITION to locate a marker.
Step 5
Replace and measure
Use REPLACE() to swap text and LENGTH()/LEN() to check how many characters a value has.
What Interviewer Expects
- Naming common functions like UPPER, LOWER, TRIM, CONCAT, SUBSTRING
- Knowing string functions help clean and standardize data
- Understanding SUBSTRING with a start and length
- Awareness that concatenation uses CONCAT or ||
- Recognizing dialect differences (LEN vs LENGTH, CHARINDEX vs POSITION)
Common Mistakes
- Assuming SQL strings are zero-indexed (SUBSTRING starts at 1)
- Using LEN in PostgreSQL instead of LENGTH
- Forgetting NULL concatenation can yield NULL without CONCAT
- Not trimming before comparing user input
- Confusing REPLACE with TRANSLATE for character-by-character mapping
Best Answer (HR Friendly)
“String functions in SQL are simple tools for working with text, like making letters uppercase, removing extra spaces, joining two fields, or pulling out part of a word. They let the database clean and format text directly, which keeps data tidy and easy to search.”
Code Example
SELECT
UPPER(first_name) AS first_upper,
LOWER(email) AS email_lower,
TRIM(city) AS clean_city,
CONCAT(first_name, ' ', last_name) AS full_name,
SUBSTRING(phone, 1, 3) AS area_code,
LENGTH(last_name) AS surname_len,
REPLACE(status, '_', ' ') AS status_label
FROM customers;SELECT id, company
FROM clients
WHERE LOWER(TRIM(company)) = LOWER(TRIM(' Acme Ltd '));Follow-up Questions
- What is the difference between CHAR and VARCHAR?
- How does string indexing start in SQL SUBSTRING?
- How do you concatenate columns when some values are NULL?
- What is the difference between REPLACE and TRANSLATE?
- How would you extract a domain from an email address?
MCQ Practice
1. Which function removes leading and trailing spaces?
TRIM() (with LTRIM/RTRIM for one side) removes leading and trailing whitespace from a string.
2. In standard SQL, SUBSTRING position indexing starts at?
SQL string positions are 1-based, so SUBSTRING(col, 1, 3) returns the first three characters.
3. Which is used to join two strings together?
CONCAT() (or the || operator) joins strings; UNION combines query result sets, not text.
Flash Cards
How do you uppercase text? — UPPER(col); use LOWER(col) for lowercase.
How do you remove surrounding spaces? — TRIM(col), or LTRIM/RTRIM for just one side.
How do you join two columns? — CONCAT(a, b) or a || b in dialects that support the || operator.
Where does SUBSTRING start counting? — At position 1 — SQL strings are 1-indexed, not 0-indexed.
How do you swap text inside a string? — REPLACE(col, 'old', 'new') replaces every occurrence of the substring.