Course contentsShow
← Data Science
Lesson 1044 of 2,14526. Advanced SQLFree lesson

String Manipulation: CONCAT, LENGTH, and SUBSTRING

Combining strings, measuring length, and extracting substrings using basic string functions.

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.