Introduction: Real-World Messy Text Data
In production databases, user-submitted text data is notoriously messy. Different users type information in different formats:
" Rahul Sharma "Leading/trailing tabs and spaces.
"PRIYA" vs "priya"Breaks grouping and filters.
"987-654-3210"Hyphens and parenthesis delimiters.
Before analyzing metrics or building reports, data professionals use SQL String Functions to sanitize and standardize text fields.
What Are SQL String Functions?
String functions are built-in SQL operations that accept text values (columns or string literals) as input, perform a transformation or extraction, and return a scalar text or numerical result.
SELECT query does not alter the underlying records stored on disk. It transforms the values dynamically in memory for the query output." rahul sharma "
"rahul sharma"
"RAHUL SHARMA"
LENGTH(): Measuring Character Count
The LENGTH() function (or LEN() in SQL Server) returns the total number of characters in a string:
FROM customers;
UPPER() & LOWER(): Case Normalization
Converting text to all-uppercase or all-lowercase standardizes fields for reporting and case-insensitive searches:
UPPER(name) AS uppercase_name,
LOWER(email) AS normalized_email
FROM customers;
TRIM(), LTRIM(), and RTRIM(): Stripping Whitespace
TRIM() strips unwanted spaces and tabs from both ends of a text string:
TRIM(first_name) AS clean_name,
LTRIM(first_name) AS left_trimmed_only,
RTRIM(first_name) AS right_trimmed_only
FROM customers;
CONCAT(): Combining Multiple Text Strings
CONCAT() merges two or more string expressions together:
FROM customers;
|| (e.g. first_name || ' ' || last_name).SUBSTRING(): Slicing Parts of a String (1-Indexed)
SUBSTRING(text, start_position, length) extracts a specific character slice from text.
SELECT SUBSTRING(order_code, 1, 4) AS order_year
FROM orders;
SUBSTRING(email, 1, 5) yields 'rahul'LEFT() and RIGHT(): Prefix & Suffix Extraction
When you need characters from the beginning or end without calculating midpoints:
LEFT(phone, 3) AS area_code,
RIGHT(phone, 4) AS last_four_digits
FROM customers;
REPLACE(): Scrubbing Delimiters & Characters
REPLACE(string, old_text, new_text) replaces all matches of old_text with new_text:
SELECT REPLACE(phone, '-', '') AS sanitized_phone
FROM customers;
"987-654-3210"
"9876543210"
POSITION() / INSTR(): Finding Substring Locations
Finding the index of a delimiter (such as @ in an email or space in a name) enables dynamic text splitting:
-- MySQL / SQLite: INSTR(email, '@')
SELECT email, POSITION('@' IN email) AS at_symbol_position
FROM customers;
Combining & Nesting String Functions
Real-world data pipelines combine multiple string functions in a single nested expression:
SELECT
UPPER(TRIM(city)) AS clean_city,
REPLACE(TRIM(phone), '-', '') AS clean_phone
FROM customers;
String Functions in WHERE Clauses for Robust Filtering
Applying LOWER() or TRIM() in your WHERE predicate prevents casing or extra spaces from breaking searches:
FROM customers
WHERE LOWER(TRIM(city)) = 'mumbai';
Classifying Text With CASE WHEN
You can evaluate string properties (such as length or prefixes) to classify records:
CASE
WHEN RIGHT(email, 4) = '.org' THEN 'Non-Profit'
WHEN RIGHT(email, 4) = '.com' THEN 'Commercial'
ELSE 'Other Domain'
END AS email_domain_type
FROM customers;
NULL Awareness in String Operations
Applying string functions to a NULL column evaluates to NULL (e.g. UPPER(NULL) ➔ NULL, LENGTH(NULL) ➔ NULL). Protect your pipelines using COALESCE():
UPPER(COALESCE(phone, 'Not Available')) AS safe_phone
FROM customers;
Common SQL String Function Mistakes
Passing index 0 into SUBSTRING() instead of 1, causing missing characters.
Assuming string comparisons match when trailing spaces cause invisible false comparisons.
Believing SELECT UPPER(name) updates the table permanently on storage.
Applying functions on nullable columns without handling fallbacks via COALESCE().
Practical Data Cleaning Exercises
email,
SUBSTRING(email, 1, POSITION('@' IN email) - 1) AS username
FROM customers;
CONCAT(UPPER(LEFT(TRIM(first_name), 1)), LOWER(SUBSTRING(TRIM(first_name), 2, 50))) AS proper_first_name,
LOWER(TRIM(email)) AS clean_email,
REPLACE(COALESCE(phone, '0000000000'), '-', '') AS digits_only_phone
FROM customers;
TRIM(first_name) AS transformed_result
FROM customers;
| id | Original Raw Value (Before) | Transformed Result (After) |
|---|---|---|
| 1 | " Rahul " | "Rahul" |
| 2 | "PRIYA" | "PRIYA" |
| 3 | " amit " | "amit" |
| 4 | "Neha" | "Neha" |
| 5 | " rohan " | "rohan" |
SQL String Function Best Practices
- Always Use TRIM() on User-Submitted Inputs: Never compare raw strings without removing accidental whitespace.
- Normalize Casing With LOWER() in Filters: Ensure case-insensitive equality matching across all database engines.
- Remember 1-Based Indexing: In ANSI SQL, character positions start at index
1. - Wrap Nullable String Operations in COALESCE(): Guard against silent NULL propagation.
What You Should Know Now: Checklist
- ✓LENGTH: Measures character counts in strings.
- ✓UPPER / LOWER: Normalizes text case for consistent filtering and display.
- ✓TRIM: Eliminates leading and trailing whitespace.
- ✓CONCAT: Merges multiple string fields together into a unified column.
- ✓SUBSTRING / LEFT / RIGHT: Slices characters using 1-based indexing.
- ✓REPLACE: Scrubs delimiters, hyphens, and unwanted characters.