This guide is specifically for PostgreSQL syntax.

SQL String Functions Quick Guide

Reference for CONCAT, SUBSTRING, TRIM, UPPER, LOWER, and pattern matching.

CONCAT

Syntax:

CONCAT(str1, str2, ...) | str1 || str2

Concatenates multiple strings. || works in PostgreSQL/Oracle.

Example:

SELECT CONCAT(first_name, ' ', last_name) as full_name FROM users;

💡 Note: CONCAT_WS adds separator between values

SUBSTRING

Syntax:

SUBSTRING(str, start, length) | SUBSTR(str, start, length)

Extracts part of a string. Start position is 1-based.

Example:

SELECT SUBSTRING(phone, 1, 3) as area_code FROM contacts;

💡 Note: Different syntax in different databases

LENGTH / LEN

Syntax:

LENGTH(str) | LENGTH(str) | CHAR_LENGTH(str)

Returns the length of a string. LEN is SQL Server, LENGTH is MySQL/PostgreSQL.

Example:

SELECT name, LENGTH(name) as name_length FROM users;

💡 Note: LEN trims trailing spaces in SQL Server

UPPER / LOWER

Syntax:

UPPER(str) | LOWER(str)

Converts string to uppercase or lowercase.

Example:

SELECT UPPER(email) as email_upper, LOWER(name) as name_lower FROM users;

💡 Note: Useful for case-insensitive comparisons

TRIM

Syntax:

TRIM(str) | LTRIM(str) | RTRIM(str)

Removes leading/trailing whitespace.

Example:

SELECT TRIM(name) as clean_name FROM users;

💡 Note: TRIM can optionally specify characters to remove

REPLACE

Syntax:

REPLACE(str, search, replace)

Replaces all occurrences of a substring.

Example:

SELECT REPLACE(phone, '-', '') as clean_phone FROM contacts;

💡 Note: Case-sensitive in most databases

LIKE Pattern Matching

Syntax:

column LIKE 'pattern'

% matches any characters, _ matches single character.

Example:

SELECT * FROM users WHERE email LIKE '%@gmail.com';

💡 Note: ILIKE for case-insensitive (PostgreSQL)

Quick Reference Table

| Function | Purpose | |----------|---------| | CONCAT | Concatenates multiple strings | | SUBSTRING | Extracts part of a string | | LENGTH / LEN | Returns the length of a string | | UPPER / LOWER | Converts string to uppercase or lowercase | | TRIM | Removes leading/trailing whitespace | | REPLACE | Replaces all occurrences of a substring | | LIKE Pattern Matching | % matches any characters, _ matches single character |

Related Cheat Sheets

Continue learning with more SQL references:

PostgreSQL-Specific Notes

This page covers PostgreSQL syntax. Other databases may have different syntax for similar operations.