Basic String Functions

So far you've worked with text data as-is: selecting it, filtering it, searching patterns. But what if you need to transform that text? Convert names to uppercase for consistency? Extract area codes from phone numbers? Combine first and last names? SQL provides powerful string functions that let you manipulate and transform text data on the fly!

📚 What You'll Learn

• How SQL manipulates text using built-in functions
• Converting text to UPPER() and LOWER() case
• Measuring text length with LENGTH() or LEN()
• Extracting portions of text with SUBSTRING()
• Trimming whitespace with TRIM(), LTRIM(), RTRIM()
• Joining strings together with CONCAT() or ||
• Replacing text with REPLACE()
• How string functions work with WHERE and LIKE
• Database differences (MySQL vs PostgreSQL vs SQL Server)
• Common mistakes beginners make
• Real-world practical examples
• Hands-on practice exercises

1. What Are String Functions?

String functions are built-in tools that transform text data. They take text as input and return modified text as output.

Basic Syntax

SELECT FUNCTION_NAME(column_name)
FROM table_name;

The function wraps around the column name and transforms its value.

2. UPPER() and LOWER() - Changing Case

These functions convert text to all uppercase or all lowercase letters.

UPPER() - Convert to Uppercase

SELECT 
    name,
    UPPER(name) AS name_uppercase
FROM students;

Result:

LOWER() - Convert to Lowercase

SELECT 
    email,
    LOWER(email) AS email_lowercase
FROM students;

Result:

Using with WHERE for Case-Insensitive Searches

-- Find "alice" regardless of how it's capitalized
SELECT name, email
FROM students
WHERE LOWER(email) LIKE '%alice%';

This matches: ALICE@..., alice@..., Alice@..., aLiCe@...

3. LENGTH() - Measuring Text Length

LENGTH() returns the number of characters in a string.

Basic Usage

SELECT 
    name,
    LENGTH(name) AS name_length
FROM students;

Result:

Using with WHERE

-- Find students with short names (less than 10 characters)
SELECT name
FROM students
WHERE LENGTH(name) < 10;

Practical Example: Validating Input

-- Find invalid phone numbers (not exactly 12 characters: XXX-XXX-XXXX)
SELECT name, phone
FROM students
WHERE LENGTH(phone) != 12;

4. SUBSTRING() - Extracting Portions of Text

SUBSTRING() extracts a portion of a string. Perfect for extracting area codes, initials, or any fixed-position data.

Syntax Variations

SUBSTRING(string, start, length)

SUBSTRING(string, start, length)

Parameters:

• string: The text to extract from
• start: Starting position (1 = first character)
• length: How many characters to extract

Example: Extracting Initials

-- Get first initial (first character)
SELECT 
    name,
    SUBSTRING(name, 1, 1) AS first_initial
FROM students;

Result:

Example: Extracting Area Code

-- Extract area code from phone (555-123-4567)
SELECT 
    phone,
    SUBSTRING(phone, 1, 3) AS area_code
FROM students;

Result:

Example: Extracting Middle Characters

-- Extract characters 5-9 from email
SELECT 
    email,
    SUBSTRING(email, 5, 5) AS middle_part
FROM students
WHERE email = 'alice@university.edu';

Starting at position 5 ('e'), extract 5 characters: 'e@uni'

5. TRIM(), LTRIM(), RTRIM() - Removing Whitespace

These functions remove extra spaces from strings. Critical for cleaning user input!

• TRIM(): Removes spaces from both ends
• LTRIM(): Removes spaces from left (beginning)
• RTRIM(): Removes spaces from right (end)

Example: Cleaning Input

SELECT 
    name,
    TRIM(name) AS name_cleaned,
    LENGTH(name) AS original_length,
    LENGTH(TRIM(name)) AS cleaned_length
FROM students;

Visualizing the Difference:

6. CONCAT() - Joining Strings Together

CONCAT() combines multiple strings into one. Essential for creating full names, formatted addresses, or custom messages.

Syntax Variations

CONCAT(string1, string2, ...)

string1 || string2 || ...

Example: Creating Full Names

-- MySQL / SQL Server
SELECT 
    first_name,
    last_name,
    CONCAT(first_name, ' ', last_name) AS full_name
FROM students;

-- PostgreSQL / Oracle
SELECT 
    first_name,
    last_name,
    first_name || ' ' || last_name AS full_name
FROM students;

Result:

Example: Creating Formatted Messages

SELECT 
    name,
    gpa,
    CONCAT(name, ' has a GPA of ', gpa) AS message
FROM students;

Result:

7. REPLACE() - Substituting Text

REPLACE() finds text within a string and replaces it with different text.

Syntax

REPLACE(string, old_text, new_text)

Example: Standardizing Phone Formats

-- Remove dashes from phone numbers
SELECT 
    phone,
    REPLACE(phone, '-', '') AS phone_no_dashes
FROM students;

Result:

Example: Fixing Typos in Data

-- Fix common misspelling
SELECT 
    major,
    REPLACE(major, 'Computor', 'Computer') AS major_corrected
FROM students;

8. Combining Functions for Complex Transformations

You can nest functions inside each other for powerful transformations:

Example: Clean and Format Names

-- Trim spaces, then convert to proper case (first letter upper)
SELECT 
    name,
    UPPER(SUBSTRING(TRIM(name), 1, 1)) AS first_letter,
    LOWER(SUBSTRING(TRIM(name), 2)) AS rest
FROM students;

Example: Extract and Format Area Code

-- Extract area code and format with parentheses
SELECT 
    phone,
    CONCAT('(', SUBSTRING(phone, 1, 3), ')') AS area_code_formatted
FROM students;

Result:

9. Using String Functions with WHERE and LIKE

String functions are incredibly useful in WHERE clauses for flexible filtering:

Example: Case-Insensitive Search

-- Find "smith" regardless of capitalization
SELECT name
FROM students
WHERE LOWER(name) LIKE '%smith%';

Example: Find Students by Name Length

-- Find students with very long names
SELECT name, LENGTH(name) AS name_length
FROM students
WHERE LENGTH(name) > 15
ORDER BY LENGTH(name) DESC;

Example: Find by Area Code

-- Find students in 555 area code using SUBSTRING
SELECT name, phone
FROM students
WHERE SUBSTRING(phone, 1, 3) = '555';

10. Common Mistakes When Using String Functions

Mistake #1: Wrong Function Name for Database

-- ❌ WRONG in SQL Server
SELECT LENGTH(name) FROM students;

-- ✅ CORRECT in SQL Server
SELECT LEN(name) FROM students;

Mistake #2: Forgetting String Positions Start at 1

-- ❌ WRONG: Position 0 doesn't exist in SQL!
SELECT SUBSTRING(name, 0, 1) FROM students;

-- ✅ CORRECT: Positions start at 1
SELECT SUBSTRING(name, 1, 1) FROM students;

Mistake #3: Not Handling NULL

-- ⚠️ If name is NULL, result is NULL
SELECT UPPER(name) FROM students;

-- ✅ BETTER: Handle NULL explicitly
SELECT COALESCE(UPPER(name), 'No name') FROM students;

Mistake #4: Wrong CONCAT Syntax

-- ❌ WRONG in MySQL (|| doesn't work)
SELECT first_name || ' ' || last_name FROM students;

-- ✅ CORRECT in MySQL
SELECT CONCAT(first_name, ' ', last_name) FROM students;

Mistake #5: Performance Issues with Functions in WHERE

-- ❌ SLOW: Function prevents index usage
WHERE UPPER(email) = 'ALICE@EXAMPLE.COM'

-- ✅ FASTER: If possible, store uppercase version
-- or use case-insensitive collation

11. 🎯 Hands-On Practice Exercises

SELECT 
    name,
    UPPER(name) AS name_uppercase
FROM students;

SELECT name, LENGTH(name) AS name_length
FROM students
WHERE LENGTH(name) > 12;

SELECT 
    name,
    SUBSTRING(name, 1, 1) AS initial
FROM students;

-- MySQL / SQL Server
SELECT 
    first_name,
    last_name,
    CONCAT(first_name, ' ', last_name) AS full_name
FROM students;

SELECT name, email
FROM students
WHERE LOWER(email) LIKE '%gmail%';

SELECT 
    phone,
    REPLACE(phone, '-', '') AS phone_digits_only
FROM students;

-- This requires functions we haven't covered yet (LOCATE/POSITION)
-- Simplified version for fixed-format emails:
SELECT 
    email,
    SUBSTRING(email, LOCATE('@', email) + 1) AS domain
FROM students;

SELECT 
    name,
    SUBSTRING(LOWER(TRIM(name)), 1, 3) AS name_code
FROM students;

📝 Key Takeaways

• UPPER() and LOWER() change text case (essential for case-insensitive searches)
• LENGTH() or LEN() measures string length in characters
• SUBSTRING() extracts portions of text (positions start at 1!)
• TRIM(), LTRIM(), RTRIM() remove whitespace
• CONCAT() or || joins strings together
• REPLACE() substitutes text within strings
• Functions can be nested for complex transformations
• String functions work with WHERE and LIKE for flexible filtering
• Syntax varies between databases—check your database documentation!
• Using functions on columns in WHERE can impact performance