String Functions
Overview
String functions allow you to transform, slice, combine, and clean textual data directly within your SQL queries.
Learning Objectives
- Combine text fields using
CONCAT()and concatenation operators. - Clean text values using
TRIM(),UPPER(), andLOWER(). - Extract and replace text patterns using
SUBSTRING()andREPLACE().
Detailed Concept Explanation
Most real-world text data is messy. SQL string functions provide standard tools for clean-up:
CONCAT(str1, str2, ...): Glues multiple text values together.UPPER(str)/LOWER(str): Standardizes text case.TRIM(str): Removes leading and trailing white spaces.LENGTH(str): Returns the character count.SUBSTRING(str, start, length): Slices text from a specified position.REPLACE(str, target, replacement): Replaces matching text occurrences.
Code Examples
SQL
-- Combine first and last names and clean up casing
SELECT
CONCAT(UPPER(last_name), ', ', TRIM(first_name)) AS formatted_name,
LENGTH(TRIM(first_name)) AS name_char_count
FROM employees;
-- Extract area code and replace formatting
SELECT
SUBSTRING(phone_number, 1, 3) AS area_code,
REPLACE(phone_number, '-', ' ') AS spaced_phone
FROM customers;
Best Practices
- Use TRIM Aggressively: Always trim foreign codes or user emails before comparing or joining on string keys to prevent white space mismatches.
- Null Cautious: Standard
CONCATmight returnNULLif any input argument isNULL. Use database-specific variants likeCONCAT_WSor null fallbacks to guard against this.
Interview Perspective
Note
Interview Question: How do you extract the domain name from an email column in SQL?
You can combine SUBSTRING with CHARINDEX/POSITION/INSTR to locate the @ index.
Example (Standard ANSI):
SELECT SUBSTRING(email FROM POSITION('@' IN email) + 1) AS domain FROM users;
Interactive Challenges
Summary
String functions sanitize, format, and slice text fields to standardize data attributes inside pipelines.
DataPlayArena Technical Editorial Board
Curated & maintained by enterprise data platform engineers with 10+ years in distributed systems.
Runtime Verification Standards: Every code example, query, and architecture diagram in this tutorial is compiled and verified against real runtimes (Apache Spark 3.5, Python 3.11, PostgreSQL, and DuckDB). If you spot any errata or want to suggest improvements, please submit feedback.