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(), and LOWER().
• Extract and replace text patterns using SUBSTRING() and REPLACE().

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 CONCAT might return NULL if any input argument is NULL. Use database-specific variants like CONCAT_WS or null fallbacks to guard against this.

Interview Perspective

Interactive Challenges

Summary

String functions sanitize, format, and slice text fields to standardize data attributes inside pipelines.