Skip to content
Tutorials Center
My learning

Topics

SQL Reference ยท Dialects and Concepts

SQL string functions across dialects

A map of SQL string function names in SQLite, PostgreSQL, MySQL, SQL Server and Oracle: length, substring, position, concat, trim, replace, padding and case.

String handling is one of the least portable parts of SQL. The idea is the same everywhere (measure, cut, search, join, clean), but the function names, argument order and NULL behavior vary. This page maps the common operations across SQLite, PostgreSQL, MySQL, SQL Server and Oracle, and shows which spellings are standard.

The standard names are CHARACTER_LENGTH, SUBSTRING(x FROM a FOR b), POSITION(a IN x), TRIM, UPPER, LOWER, OVERLAY and the || operator. Only some databases implement all of them.

SQL
SELECT UPPER(name) AS shout, LENGTH(name) AS chars FROM categories LIMIT 3;

Try it

SQL The SQLite spellings ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

SQL Trimming, case and NULL in concatenation ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Dialect differences

Operation SQLite PostgreSQL MySQL SQL Server Oracle
Length in characters LENGTH(x) LENGTH(x), CHAR_LENGTH(x) CHAR_LENGTH(x) LEN(x) (ignores trailing spaces) LENGTH(x)
Substring SUBSTR(x, start, len) SUBSTRING(x, start, len) or SUBSTR SUBSTRING(x, start, len) SUBSTRING(x, start, len) (all three args required) SUBSTR(x, start, len)
Position of a substring INSTR(x, sub) POSITION(sub IN x), STRPOS(x, sub) LOCATE(sub, x), INSTR(x, sub) CHARINDEX(sub, x) INSTR(x, sub)
Concatenate a || b a || b, CONCAT(a, b) CONCAT(a, b) a + b, CONCAT(a, b) a || b, CONCAT(a, b)
Join with separator none (use ||) CONCAT_WS(sep, ...) CONCAT_WS(sep, ...) CONCAT_WS(sep, ...) none (use ||)
Replace REPLACE(x, a, b) REPLACE(x, a, b) REPLACE(x, a, b) REPLACE(x, a, b) REPLACE(x, a, b)
Trim TRIM, LTRIM, RTRIM TRIM, BTRIM, LTRIM, RTRIM TRIM, LTRIM, RTRIM TRIM (2017+), LTRIM, RTRIM TRIM, LTRIM, RTRIM
Left-pad none built in LPAD(x, n, pad) LPAD(x, n, pad) none (RIGHT(REPLICATE(...))) LPAD(x, n, pad)
Leftmost n chars SUBSTR(x, 1, n) LEFT(x, n) LEFT(x, n) LEFT(x, n) SUBSTR(x, 1, n)
Reverse none built in REVERSE(x) REVERSE(x) REVERSE(x) REVERSE(x) (undocumented)
Repeat none built in REPEAT(x, n) REPEAT(x, n) REPLICATE(x, n) RPAD trick
Regex match none by default x ~ 'pattern' x REGEXP 'pattern' none (use LIKE/PATINDEX) REGEXP_LIKE(x, 'pattern')
SQL
-- PostgreSQL
SELECT STRPOS('database', 'base'), LEFT('database', 4), LPAD('7', 3, '0');

-- MySQL
SELECT LOCATE('base', 'database'), LEFT('database', 4), LPAD('7', 3, '0');

-- SQL Server
SELECT CHARINDEX('base', 'database'), LEFT('database', 4), FORMAT(7, '000');

-- Oracle
SELECT INSTR('database', 'base'), SUBSTR('database', 1, 4), LPAD('7', 3, '0') FROM dual;

All of these return 5, data and 007 for the same inputs.

Differences that change results

  • Concatenating NULL. With || in SQLite and PostgreSQL, and with + in SQL Server (by default), the result is NULL if any piece is NULL. CONCAT() in PostgreSQL and SQL Server treats NULL as an empty string, but MySQL's CONCAT() returns NULL if any argument is NULL. Oracle's || and CONCAT skip NULL pieces, because Oracle treats '' as NULL.
  • Empty string. In Oracle, '' is NULL, so LENGTH('') is NULL. Elsewhere it is 0.
  • Characters or bytes. SQLite's LENGTH counts characters for text; MySQL's LENGTH counts bytes (use CHAR_LENGTH); SQL Server's LEN counts characters and drops trailing spaces; DATALENGTH counts bytes.
  • Negative start. SQLite's SUBSTR('database', -4) returns the last four characters (base). Do not copy that idiom to other systems: in PostgreSQL a start below 1 simply returns the whole string, and in SQL Server a negative length is an error.
  • Case sensitivity of comparisons depends on collation: case-insensitive by default in MySQL and SQL Server, case-sensitive in PostgreSQL and Oracle.

Usage notes

  • Use COALESCE before concatenating nullable columns, so one missing middle name does not erase the whole name.
  • Keep SQL for storage-level work. Heavy parsing and formatting of text is easier and more portable in application code.
  • Functions on columns defeat indexes. WHERE LOWER(email) = 'x' cannot use a plain index on email; create an expression index (PostgreSQL, SQLite, Oracle) or a case-insensitive collation.
  • Positions start at 1, not 0, in every dialect. A "not found" result is 0 for INSTR, LOCATE, CHARINDEX and POSITION.
  • sql.js here bundles extras (REVERSE, PADL, ...) that stock SQLite lacks. Do not depend on them in portable code.