String Functions

CONCAT(str1, str2, ...)
-- or ANSI: str1 || str2

SELECT
  CONCAT(first_name, ' ', last_name) AS full_name,
  first_name || ' ' || last_name AS full_name_ansi
FROM users;

-- full_name     | full_name_ansi
-- Alice Johnson | Alice Johnson

Note The || operator is ANSI standard and works in PostgreSQL. MySQL uses CONCAT() only. In MySQL, CONCAT returns NULL if any argument is NULL. In PostgreSQL, || with a NULL also returns NULL. Use COALESCE to handle NULLs.

SUBSTRING(string FROM start FOR length)
SUBSTRING(string, start, length)

SELECT
  SUBSTRING(phone FROM 1 FOR 3) AS area_code,
  SUBSTRING(email FROM POSITION('@' IN email) + 1) AS domain
FROM users;

-- area_code | domain
-- 206       | example.com

Note Positions are 1-based in SQL (not 0-based like most programming languages). The FROM/FOR syntax is ANSI; the comma syntax works in MySQL and PostgreSQL.

UPPER(string)
LOWER(string)

SELECT
  UPPER(last_name) AS last_upper,
  LOWER(email) AS email_lower
FROM users;

-- last_upper | email_lower
-- JOHNSON    | [email protected]

Note Useful for case-insensitive comparisons: WHERE LOWER(email) = LOWER('[email protected]'). For better performance on frequent case-insensitive lookups, create a functional index on LOWER(column). PostgreSQL also offers ILIKE.

TRIM([LEADING|TRAILING|BOTH] [chars] FROM string)
LTRIM(string)
RTRIM(string)

SELECT
  TRIM('  Alice  ') AS trimmed,
  TRIM(LEADING '0' FROM '000425') AS no_leading_zeros,
  LTRIM('  hello') AS left_trimmed,
  RTRIM('hello  ') AS right_trimmed;

-- trimmed | no_leading_zeros | left_trimmed | right_trimmed
-- Alice   | 425              | hello        | hello

Note TRIM with no arguments removes whitespace from both sides. You can specify characters to trim. LTRIM and RTRIM are shorthand for leading/trailing trim. Clean user input with TRIM before storing.

LENGTH(string)
CHAR_LENGTH(string)

SELECT
  first_name,
  CHAR_LENGTH(first_name) AS name_length
FROM users
WHERE CHAR_LENGTH(first_name) > 10;

-- first_name  | name_length
-- Christopher | 11

Note CHAR_LENGTH counts characters; LENGTH counts bytes. They differ for multi-byte character sets (UTF-8). Use CHAR_LENGTH for user-facing string length. ANSI standard is CHARACTER_LENGTH.

REPLACE(string, search, replacement)

SELECT
  REPLACE(phone, '-', '') AS digits_only,
  REPLACE(product_name, 'V1', 'V2') AS updated_name
FROM products;

-- digits_only | updated_name
-- 2065551234  | Widget V2 Pro

Note REPLACE is case-sensitive in PostgreSQL and MySQL. It replaces all occurrences, not just the first one. For regex-based replacement in PostgreSQL, use REGEXP_REPLACE.

COALESCE(value1, value2, ..., default)

SELECT
  first_name,
  COALESCE(nickname, first_name) AS display_name,
  COALESCE(phone, email, 'No contact') AS primary_contact
FROM users;

-- Returns the first non-NULL value from the list

Note COALESCE accepts any number of arguments and returns the first non-NULL. It is ANSI standard and works everywhere. Use it for NULL fallback chains. It is NOT specific to strings - works with any data type.

CAST(expression AS data_type)
expression::data_type  -- PostgreSQL shorthand

SELECT
  CAST(price AS INTEGER) AS rounded_price,
  CAST(order_date AS VARCHAR) AS date_string,
  '42'::INTEGER + 8 AS sum_pg  -- PostgreSQL only
FROM products;

-- rounded_price | date_string | sum_pg
-- 29             | 2025-03-15  | 50

Note CAST is ANSI standard. PostgreSQL's :: shorthand is shorter but not portable. Be careful casting - CAST('abc' AS INTEGER) will throw an error. Use TRY_CAST in SQL Server for safe conversions.

POSITION(substring IN string)
STRPOS(string, substring)  -- PostgreSQL

SELECT
  email,
  POSITION('@' IN email) AS at_position,
  SUBSTRING(email FROM POSITION('@' IN email) + 1) AS domain
FROM users;

-- email             | at_position | domain
-- [email protected] | 6           | example.com

Note Returns the 1-based position of the first occurrence. Returns 0 if not found (not -1 like most programming languages). MySQL uses LOCATE(substring, string) which has the arguments in reverse order.

LPAD(string, target_length, pad_char)
RPAD(string, target_length, pad_char)

SELECT
  LPAD(CAST(invoice_number AS VARCHAR), 8, '0') AS padded_invoice,
  RPAD(product_code, 10, '.') AS padded_code
FROM invoices;

-- padded_invoice | padded_code
-- 00004271       | PRD-42....

Note LPAD pads on the left, RPAD on the right. If the string is already longer than target_length, it gets truncated to target_length. Commonly used for formatting invoice numbers, report columns, and display output.