Strings and dates Updated November 2026

String functions

Standard string operations in SQL: first_name || ' ' || last_name concatenates, UPPER(s) and LOWER(s) change case, LENGTH(s) counts characters, SUBSTR(s, start, len) cuts a piece (1-based), TRIM(s) strips spaces and REPLACE(s, old, new) swaps text. All work in SQLite and PostgreSQL; position is INSTR in SQLite and POSITION in PostgreSQL.

Syntax

s1 || s2 | UPPER(s) | LOWER(s) | LENGTH(s) | SUBSTR(s, start, length) | TRIM(s) | REPLACE(s, from, to)

Examples

SELECT first_name || ' ' || last_name AS full_name FROM employees;

Result: full_name Ana Croes

If either part is NULL the result is NULL; wrap with COALESCE(last_name, ''). PostgreSQL also has CONCAT(), which ignores NULLs.

SELECT UPPER(country) AS country, LENGTH(name) AS name_len FROM customers;

Result: country | name_len ARUBA | 9

SELECT email, SUBSTR(email, INSTR(email, '@') + 1) AS domain FROM customers;

Result: email | domain ana@gmail.com | gmail.com

SQLite. PostgreSQL: SUBSTR(email, POSITION('@' IN email) + 1) or SPLIT_PART(email, '@', 2).

SELECT REPLACE(TRIM(phone), '-', '') AS clean_phone FROM customers;

Result: ' 555-1234 ' becomes 5551234

Common mistakes

Using + to join strings (as in SQL Server) gives 0 or an error in SQLite and PostgreSQL; use ||.
Treating SUBSTR as zero-based: SUBSTR('hello', 1, 2) is 'he'; position 0 is not the first character.
Comparing with = when case differs ('Aruba' vs 'aruba') in PostgreSQL; compare LOWER(a) = LOWER(b) or use ILIKE.

Practice String functions

These drills check your answer on live data, free, no account needed.

Pattern matchReturn the names of products whose name contains the letter o, in upper or lower case.

Questions

How do I concatenate strings in SQL?

Use || in SQLite, PostgreSQL and Oracle: a || ' ' || b. MySQL and PostgreSQL also have CONCAT(a, ' ', b); SQL Server uses + or CONCAT.

How do I get the first N characters of a string?

SUBSTR(s, 1, N) in SQLite and PostgreSQL; LEFT(s, N) in PostgreSQL, MySQL and SQL Server.

How do I split a string on a delimiter in SQL?

PostgreSQL: SPLIT_PART(s, ',', 2) for a piece or STRING_TO_ARRAY for all parts. SQLite has no split function; combine SUBSTR and INSTR, or use the JSON functions.

LIKELIKE matches text against a pattern: % stands for any run of characters and _ for one character. Starts with, ends with, contains, case sensitivity, NOT LIKE.
CASTCAST(value AS type) converts between types: string to integer, integer to decimal for division, number to text for concatenation, text to date, plus :: form.
COALESCECOALESCE returns the first non-NULL argument. Show a default instead of NULL, treat missing numbers as zero in sums, fall back across columns, IFNULL, NULLIF.
Aliases (AS)An alias gives a column or table a temporary name: SELECT price * qty AS total, FROM orders o. Where aliases are allowed (ORDER BY yes, WHERE no) and quoting.