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.
SELECT UPPER(name) AS shout, LENGTH(name) AS chars FROM categories LIMIT 3;
Try it
Press Run (or Ctrl + Enter) to see the result here.
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') |
-- 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 isNULLif any piece isNULL.CONCAT()in PostgreSQL and SQL Server treatsNULLas an empty string, but MySQL'sCONCAT()returnsNULLif any argument isNULL. Oracle's||andCONCATskipNULLpieces, because Oracle treats''asNULL. - Empty string. In Oracle,
''isNULL, soLENGTH('')isNULL. Elsewhere it is0. - Characters or bytes. SQLite's
LENGTHcounts characters for text; MySQL'sLENGTHcounts bytes (useCHAR_LENGTH); SQL Server'sLENcounts characters and drops trailing spaces;DATALENGTHcounts 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
COALESCEbefore 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 onemail; 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
0forINSTR,LOCATE,CHARINDEXandPOSITION. - sql.js here bundles extras (
REVERSE,PADL, ...) that stock SQLite lacks. Do not depend on them in portable code.