String Manipulation: CONCAT, LENGTH, and SUBSTRING
You'll learn how to combine, measure, and extract parts of text data using three fundamental SQL string functions.
The Core Idea
Just like you might glue pieces of paper together, measure a ribbon's length, or cut out a section of fabric, SQL lets you manipulate text strings in similar ways:
- CONCAT glues strings together
- LENGTH measures how many characters are in a string
- SUBSTRING extracts a specific portion of a string
These functions are essential when cleaning data, formatting output, or extracting meaningful information from text columns.
How They Work
CONCAT combines two or more strings into one:
SELECT CONCAT(first_name, ' ', last_name) AS full_name
FROM employees;
-- Result: 'John Doe'
LENGTH counts the number of characters in a string:
SELECT product_name, LENGTH(product_name) AS name_length
FROM products
WHERE LENGTH(product_name) > 20;
-- Finds products with long names
SUBSTRING extracts a portion of a string by specifying a starting position and optional length:
SELECT SUBSTRING(email, 1, 5) AS email_prefix
FROM users;
-- Extracts first 5 characters: 'john@...' becomes 'john@'
Practical Example
These functions often work together. Imagine extracting area codes from phone numbers and creating formatted labels:
SELECT
CONCAT('User: ', username) AS label,
SUBSTRING(phone, 1, 3) AS area_code,
LENGTH(description) AS desc_length
FROM customer_profiles
WHERE LENGTH(description) < 100;
Key Takeaway: CONCAT combines strings, LENGTH measures their size, and SUBSTRING extracts specific portions—three essential tools for cleaning and transforming text data in SQL.