String Functions – Guided Study Workspace

Introduction: Real-World Messy Text Data

In production databases, user-submitted text data is notoriously messy. Different users type information in different formats:

Messy Spacing:

" Rahul Sharma "
Leading/trailing tabs and spaces.

Inconsistent Casing:

"PRIYA" vs "priya"
Breaks grouping and filters.

Punctuation Noise:

"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.

Key Principle: Non-Destructive Transformations

Using string functions in a SELECT query does not alter the underlying records stored on disk. It transforms the values dynamically in memory for the query output.

Diagram 1: Multi-Stage Text Cleaning Pipeline

Raw Input
" rahul sharma "

── TRIM() ──▶

Whitespace Removed
"rahul sharma"

── UPPER() ──▶

Sanitized Output
"RAHUL SHARMA"

LENGTH(): Measuring Character Count

The LENGTH() function (or LEN() in SQL Server) returns the total number of characters in a string:

SELECT name, LENGTH(name) AS char_count
FROM customers;

UPPER() & LOWER(): Case Normalization

Converting text to all-uppercase or all-lowercase standardizes fields for reporting and case-insensitive searches:

SELECT
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:

SELECT
    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:

SELECT CONCAT(first_name, ' ', last_name) AS full_name
FROM customers;

Dialect Note: PostgreSQL, SQLite, and Oracle also support the standard ANSI string concatenation operator || (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.

-- Extracts 4 characters starting at position 1 (returns year)
SELECT SUBSTRING(order_code, 1, 4) AS order_year
FROM orders;

1-Based Indexing: Unlike Python, C++, or JavaScript (where strings start at 0), standard SQL starts counting character positions at 1!

Diagram 2: 1-Indexed SUBSTRING Extraction

r   a   h   u   l  @   e   x   a   m   p   l   e
Positions: 1   2   3   4   5 ➔ SUBSTRING(email, 1, 5) yields 'rahul'

LEFT() and RIGHT(): Prefix & Suffix Extraction

When you need characters from the beginning or end without calculating midpoints:

SELECT
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:

-- Removes hyphens to produce clean 10-digit number (9876543210)
SELECT REPLACE(phone, '-', '') AS sanitized_phone
FROM customers;

Diagram 3: REPLACE Punctuation Stripping

Delimited String
"987-654-3210"

── REPLACE('-', '') ──▶

Sanitized Digits
"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:

-- PostgreSQL / ANSI SQL: POSITION('@' IN email)
-- 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:

-- Trims whitespace, removes hyphens, and formats phone
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:

SELECT *
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:

SELECT name, email,
    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():

SELECT
UPPER(COALESCE(phone, 'Not Available')) AS safe_phone
FROM customers;

Common SQL String Function Mistakes

1. Forgetting 1-Based Indexing

Passing index 0 into SUBSTRING() instead of 1, causing missing characters.

2. Forgetting to TRIM Whitespace

Assuming string comparisons match when trailing spaces cause invisible false comparisons.

3. Assuming Tables Are Modified on Disk

Believing SELECT UPPER(name) updates the table permanently on storage.

4. Overlooking NULL Propagation

Applying functions on nullable columns without handling fallbacks via COALESCE().

Practical Data Cleaning Exercises

Exercise 1: Extract Email Username (Before '@')

SELECT
email,
SUBSTRING(email, 1, POSITION('@' IN email) - 1) AS username
FROM customers;

Exercise 2: Full Customer Contact Card Standardization

SELECT
    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;

⚡ Live Interactive SQL String Functions Lab

Test live text transformations, before vs after diffs, and multi-step data cleaning pipelines

Select Function:

Target Column:

Generated SQL Statement

SELECT first_name,
TRIM(first_name) AS transformed_result
FROM customers;

Transformation Output (Before vs. After Diff)Function: TRIM() on first_name

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.

TEST YOUR KNOWLEDGE

Knowledge Assessment: SQL String Functions

Test your understanding of text manipulation, 1-based substring slicing, case normalization, trimming whitespace, and multi-step cleaning pipelines.

Question 1 of 8Current Score: 0 / 0

What is the standard indexing base for character positions in SQL string functions (such as SUBSTRING and POSITION)?