String functions

CONCAT, SUBSTRING, LENGTH, UPPER, LOWER, REPLACE and TRIM.

What you'll be able to do

Manipulate text columns
Extract parts of a string
Know which string operations block index use

Joining and measuring

CONCAT(first_name, ' ', last_name)   -- also: first_name || ' ' || last_name
LENGTH(name)                          -- LEN() in SQL Server

The || operator is standard SQL and works in Postgres, Oracle and SQLite. SQL Server uses +, which is why CONCAT is the portable choice.

A null anywhere in || makes the whole result null; CONCAT treats nulls as empty strings in most dialects. That difference catches people out.

Case and trimming

UPPER(city)
LOWER(email)
TRIM(name)          -- both ends
LTRIM(name)         -- left only
RTRIM(name)         -- right only

Trailing whitespace is a frequent cause of joins failing to match on values that look identical.

Extracting parts

SUBSTRING(phone FROM 1 FOR 3)    -- standard
SUBSTRING(phone, 1, 3)           -- MySQL, SQL Server
LEFT(name, 5)
RIGHT(name, 3)
POSITION('@' IN email)           -- CHARINDEX() in SQL Server

Extracting a domain from an email combines two of them:

SUBSTRING(email, POSITION('@' IN email) + 1)

Replacing

REPLACE(product_name, 'Corp.', 'Corporation')

Returns a modified copy; the stored value is untouched.

The performance note

Wrapping a filtered column in a function prevents an index seek:

WHERE UPPER(city) = 'HOUSTON'    -- cannot use an index on city

On a large table, either store a normalised copy of the column, or create a functional index where your database supports one.

Key points

CONCAT handles nulls more gracefully than || in most dialects
Trailing whitespace silently breaks joins — TRIM on import
A function around a filtered column prevents index use

Check yourself