beginner

String Functions

By DataPlayArena Technical Review BoardUpdated 2026-07-236 min readRuntime Verified

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

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

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

Challenge 1: Format Product Names

Write a query to select the 'name' column from the 'products' table converted to all uppercase characters, naming the alias 'uppercased_name'.


Summary

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

DPA

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.