SQL String Functions — CONCAT, SUBSTRING, TRIM, REPLACE

Working with Text in SQL

String functions let you manipulate text values directly in your queries — concatenating, extracting substrings, changing case, and more.

Concatenation

-- Standard SQL / PostgreSQL
SELECT first_name || ' ' || last_name AS full_name FROM employees;

-- MySQL
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM employees;

Case Conversion

SELECT UPPER(name) AS upper_name, LOWER(email) AS lower_email
FROM employees;

Substring Extraction

-- Extract first 3 characters
SELECT SUBSTRING(name, 1, 3) AS initials FROM employees;

-- PostgreSQL also supports LEFT/RIGHT
SELECT LEFT(name, 3), RIGHT(email, 10) FROM employees;

Trimming Whitespace

SELECT TRIM(name) AS trimmed FROM employees;
SELECT LTRIM(name), RTRIM(name) FROM employees;

-- Trim specific characters (PostgreSQL)
SELECT TRIM(BOTH '-' FROM slug) FROM articles;

Replace and Length

SELECT REPLACE(email, '@old.com', '@new.com') AS migrated_email
FROM employees;

SELECT name, LENGTH(name) AS name_length FROM employees;

Dialect-Specific Highlights

PostgreSQL:

• INITCAP(str) — capitalize first letter of each word
• REGEXP_REPLACE(str, pattern, replacement, flags)
• STRING_AGG(col, delimiter) — concatenate group values

MySQL:

• CONCAT_WS(separator, str1, str2, ...) — concat with separator
• LOCATE(substr, str) — find position
• LPAD(str, len, pad) / RPAD — pad strings
• GROUP_CONCAT(col SEPARATOR ',')

SQLite:

• SUBSTR(str, start, length) instead of SUBSTRING
• GROUP_CONCAT(col, separator)
• Limited built-in string functions; no regex by default