String Functions

Almost every real query eventually needs to manipulate text — combine two columns into a full name, trim stray whitespace, check the length of a value, or search inside a string. Every relational database ships a set of string functions to do this, but here is the catch: string function names vary between databases more than almost any other part of SQL. The concepts below are universal; the exact function name to call is not.

Common string functions by dialect

Operation	PostgreSQL	MySQL	SQL Server	Oracle
Concatenation	`\|\|` or `CONCAT()`	`CONCAT()`	`+` or `CONCAT()`	`\|\|` or `CONCAT()`
String length	`LENGTH()`	`LENGTH()` / `CHAR_LENGTH()`	`LEN()`	`LENGTH()`
Upper / lower case	`UPPER()` / `LOWER()`	`UPPER()` / `LOWER()`	`UPPER()` / `LOWER()`	`UPPER()` / `LOWER()`
Trim whitespace	`TRIM()`, `LTRIM()`, `RTRIM()`	`TRIM()`, `LTRIM()`, `RTRIM()`	`TRIM()`, `LTRIM()`, `RTRIM()`	`TRIM()`, `LTRIM()`, `RTRIM()`
Substring	`SUBSTRING()`	`SUBSTRING()` / `MID()`	`SUBSTRING()`	`SUBSTR()`
Replace text	`REPLACE()`	`REPLACE()`	`REPLACE()`	`REPLACE()`
Find position of substring	`POSITION(sub IN str)` / `STRPOS()`	`LOCATE()` / `INSTR()`	`CHARINDEX()`	`INSTR()`

Concatenation

Joining strings together is the most common string operation of all — combining a first and last name, or building a formatted label. Postgres and Oracle support the ANSI-standard || operator; SQL Server historically used +; and CONCAT() works (with minor differences in NULL handling) across all major databases, which makes it the safest choice for portable code.

Concatenation — portable vs dialect-specific

SQL

-- Works on PostgreSQL, MySQL, SQL Server, Oracle (most common denominator)
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM employees;

-- PostgreSQL / Oracle shorthand
SELECT first_name || ' ' || last_name AS full_name FROM employees;

-- SQL Server shorthand
SELECT first_name + ' ' + last_name AS full_name FROM employees;

Trimming, case, and length

Trim, case conversion, length

SQL

SELECT
  TRIM('  hello  ')          AS trimmed,       -- 'hello'
  UPPER(email)               AS shout_email,
  LOWER(email)                AS normalized_email,
  LENGTH(email)                AS email_length
FROM users;

Substrings and replacing text

SUBSTRING() extracts a slice of a string starting at a given position, and REPLACE() swaps every occurrence of one substring for another:

Substring and replace

SQL

-- Extract the first 3 characters of a product code
SELECT SUBSTRING(product_code, 1, 3) AS code_prefix FROM products;

-- Replace dashes with spaces in a formatted phone number
SELECT REPLACE(phone, '-', ' ') AS phone_display FROM customers;

Finding a substring's position

This is one of the clearest examples of naming divergence: the standard function is POSITION(substring IN string), but SQL Server calls the same concept CHARINDEX(), and MySQL and Oracle offer LOCATE()/INSTR() with the argument order flipped from POSITION().

Finding position (dialect-specific)

SQL

-- PostgreSQL / ANSI standard
SELECT POSITION('@' IN email) AS at_symbol_index FROM users;

-- SQL Server
SELECT CHARINDEX('@', email) AS at_symbol_index FROM users;

-- MySQL / Oracle
SELECT INSTR(email, '@') AS at_symbol_index FROM users;

Tip

String functions vary more by dialect than almost anything else in SQL. Before assuming a function name, check your specific database's documentation — a query that works perfectly on PostgreSQL may fail outright, or silently do the wrong thing, on SQL Server or MySQL.