String functions

Concatenation

Loading editor…

|| is the SQL standard operator (Postgres, SQLite, Oracle, and SQL Server 2012+ support it; MySQL does not — it reads || as logical OR unless PIPES_AS_CONCAT is set). The portable function is CONCAT(...), supported by Postgres, MySQL, and SQL Server.

The catch is NULL:

Loading editor…

|| propagates NULL — one NULL input makes the whole result NULL. CONCAT() treats NULL as an empty string. If you want a separator between non-null parts, CONCAT_WS(sep, ...) skips the nulls: CONCAT_WS('-', a, b, c).

Length

Loading editor…

The SQL Server LEN trailing-space quirk bites people: LEN('hi ') is 2, not 5. Use DATALENGTH or LEN(s + '|') - 1 if trailing spaces matter.

Upper and lower case

Loading editor…

UPPER / LOWER are the same everywhere. (Case-insensitive comparison is better done with the engine's collation than by upper-casing both sides in every query.)

Trimming

Loading editor…

TRIM / LTRIM / RTRIM remove whitespace by default. To trim a specific character set, SQLite takes a second argument: trim(s, 'x'). The SQL standard spells it TRIM(BOTH 'x' FROM s) (Postgres, MySQL, Oracle); SQL Server only gained TRIM(chars FROM s) in 2022.

Substrings

Loading editor…

substr(s, start, length) — positions are 1-indexed, and a negative start counts back from the end. The standard spelling is SUBSTRING(s FROM start FOR length) (Postgres, MySQL); SQL Server uses SUBSTRING(s, start, length).

For the first or last n characters, most engines have LEFT(s, n) / RIGHT(s, n) — but SQLite has neither. The equivalents there are substr(s, 1, n) and substr(s, -n).

Finding a position

Loading editor…

Returns a 1-indexed position, or 0 when the substring isn't present. The name is the least portable part:

Note MySQL's INSTR and SQLite's take arguments in (haystack, needle) order, but POSITION reads needle IN haystack — easy to flip.

Replacing

Loading editor…

REPLACE(s, from, to) replaces every occurrence, and is consistent across all four engines. There's no "replace first only" — for that you're into substr + instr arithmetic or a regex function.

Padding

LPAD(s, len, pad) / RPAD(s, len, pad) exist in Postgres, MySQL, and Oracle, but not in SQLite or SQL Server. In SQLite, printf (aliased format in 3.44+) covers the common case:

Loading editor…

Splitting on a delimiter

Genuinely not portable — there's no standard, and SQLite has nothing built in:

-- Postgres: 1-indexed, returns one part
SPLIT_PART('a,b,c', ',', 2)              -- 'b'

-- MySQL: returns everything up to the Nth delimiter
SUBSTRING_INDEX('a,b,c', ',', 2)         -- 'a,b'
SUBSTRING_INDEX(SUBSTRING_INDEX('a,b,c', ',', 2), ',', -1)  -- 'b'

-- SQL Server: returns a one-column table (use in FROM / APPLY)
SELECT value FROM STRING_SPLIT('a,b,c', ',')

In SQLite you build it by hand with instr + substr:

Loading editor…

Gotchas

• Positions are 1-indexed in every engine's string functions — substr(s, 1, n) starts at the first character, not the second.
• || vs CONCAT disagree on NULL — || gives NULL if any operand is NULL; CONCAT treats NULL as ''. Pick deliberately.
• LEFT / RIGHT / LPAD / RPAD aren't universal — missing from SQLite (all four) and SQL Server (the pad functions). Reach for substr or printf there.
• LEN ignores trailing spaces on SQL Server; LENGTH elsewhere does not. This changes comparisons and padding math.
• Byte length ≠ character length for non-ASCII text. Know which one the function you're calling returns.

Safety checklist

• decide || vs CONCAT based on how you want NULL handled, and be consistent
• remember positions are 1-indexed when translating from a zero-indexed language
• before shipping, check every string function you used exists on the target engine — several don't