SQL / TEXT, NUMERIC, AND DATE FUNCTIONS
Writing portable SQL across database engines
Pick the function spellings that run unchanged on PostgreSQL, MySQL, SQL Server, SQLite and Oracle, and isolate the differences you cannot avoid.
What you will learn
- Use COALESCE, never IFNULL, ISNULL or NVL, when several engines must run the query
- Check a function on every target engine and version before depending on it
- Cast text to numbers yourself instead of relying on each engine's implicit rules
- Keep native functions in one view or dialect module so queries stay identical
Understanding Writing portable SQL across database engines
The SQL standard gives every engine a shared vocabulary, but each vendor implements a different subset of it and adds its own spellings on top. EXTRACT(YEAR FROM d) is standard and works on PostgreSQL, MySQL and Oracle, yet SQL Server and SQLite reject it; YEAR(d) works on MySQL and SQL Server and on none of the other three. The useful question is therefore never 'is this standard?' but 'does every engine on my list have this, with the same argument order and the same result type?'
Matching names are not matching behaviour, and that is where portable-looking code breaks quietly. SQL Server's LEN ignores trailing spaces while LENGTH counts them elsewhere; the usual default collations of MySQL and SQL Server make 'ABC' = 'abc' true while PostgreSQL, Oracle and SQLite say false; Oracle stores the empty string as NULL, so a COALESCE fallback fires there for rows it ignores everywhere else. Treat each function as four things that must all agree - name, argument order, result type, and behaviour on NULL, empty strings and overflow - because only a mismatched name gives you an error instead of a wrong answer.
Some differences cannot be removed, so the goal is to have exactly one place that knows about them. Write application queries in the shared core (COALESCE, CASE, CAST, NULLIF, ABS, UPPER, LOWER, CURRENT_TIMESTAMP) and move anything native behind a view, a generated column or a per-engine dialect module, so the query text callers write never changes. Then run the same tests against every target engine, because portability is verified, not intended: a query that has only ever run on one engine is not portable, it is untested.
CREATE TABLE booking (
code VARCHAR(10) NOT NULL,
city VARCHAR(20),
nights INTEGER NOT NULL
);
INSERT INTO booking (code, city, nights) VALUES ('BK-1', 'Lisbon', 3);
INSERT INTO booking (code, city, nights) VALUES ('BK-2', NULL, 1);
-- Every construct below exists on PostgreSQL, MySQL, SQL Server, SQLite and Oracle.
-- The tempting shortcuts do not: IFNULL (MySQL, SQLite only), ISNULL (SQL Server,
-- but one argument with a different meaning on MySQL), NVL (Oracle only).
SELECT code,
UPPER(COALESCE(city, 'unassigned')) AS city,
CASE WHEN nights > 2 THEN 'long' ELSE 'short' END AS stay,
nights * 2 AS bed_nights
FROM booking
ORDER BY code;Portability is defined by the intersection of the engines you actually target, not by the SQL standard, so every function must be confirmed on each target and the leftovers pushed into one replaceable layer.
Worked examples
One date part, four spellings
Shows that the year of a date has no single spelling that all common engines accept, standard or not.
-- runs on PostgreSQL and MySQL (Oracle needs a trailing FROM dual):
SELECT EXTRACT(YEAR FROM DATE '2026-05-04') AS yr;
-- SQL Server: SELECT DATEPART(year, CAST('2026-05-04' AS DATE)) AS yr;
-- SQLite: SELECT CAST(strftime('%Y', '2026-05-04') AS INTEGER) AS yr;
-- YEAR('2026-05-04') works on MySQL and SQL Server, not on the other three.Example explained
Line 1EXTRACT(YEAR FROM ...) is the standard spelling and covers PostgreSQL, MySQL, MariaDB, Oracle and Db2.
Line 2SQL Server and SQLite have no EXTRACT at all and reject the query as invalid syntax, so standard does not imply available.
Line 3YEAR() looks like an escape hatch but covers a different pair of engines, which is why neither form can be your default.
Line 4The typed literal DATE '2026-05-04' is itself dialect-bound: SQL Server and SQLite need a plain string plus a cast.
Pin the dialect to a view
Moves the engine-specific date function into a view so the reporting query is byte-identical everywhere.
-- SQLite build of the compatibility layer
CREATE TABLE payment (id INTEGER, paid_on TEXT);
INSERT INTO payment (id, paid_on) VALUES (1, '2025-11-07');
INSERT INTO payment (id, paid_on) VALUES (2, '2026-02-18');
INSERT INTO payment (id, paid_on) VALUES (3, '2026-06-02');
CREATE VIEW payment_v AS
SELECT id,
paid_on,
CAST(strftime('%Y', paid_on) AS INTEGER) AS paid_year
FROM payment;
-- PostgreSQL body: CAST(EXTRACT(YEAR FROM paid_on) AS INTEGER)
-- SQL Server body: DATEPART(year, paid_on)
-- application query, unchanged on every engine:
SELECT paid_year, COUNT(*) AS n
FROM payment_v
GROUP BY paid_year
ORDER BY paid_year;Example explained
Line 1strftime exists only in SQLite, so the view definition is the one artefact that differs per engine.
Line 2The CAST is needed because strftime returns text; without it paid_year would sort, join and compare as a string.
Line 3GROUP BY paid_year names a real view column, which is portable, unlike grouping by a SELECT-list alias that SQL Server and Oracle reject.
Line 4Callers never see a dialect function, so adding a fourth engine means writing one view, not editing every report.
Explicit casts beat implicit conversion
Demonstrates the same text-to-number comparison giving a different answer on each engine unless you cast yourself.
-- run on SQLite
CREATE TABLE part (code TEXT, qty INTEGER);
INSERT INTO part VALUES ('007', 2);
INSERT INTO part VALUES ('42', 9);
SELECT COUNT(*) AS implicit_match FROM part WHERE code = 7;
SELECT COUNT(*) AS explicit_match FROM part WHERE CAST(code AS DECIMAL(10,0)) = 7;Example explained
Line 1SQLite applies the column's TEXT affinity to the literal, comparing '007' with '7' and matching nothing - no error, just a silently empty count.
Line 2MySQL converts in the other direction, turning '007' into 7, so the identical predicate returns 1 there.
Line 3PostgreSQL refuses the comparison entirely: there is no operator for text equals integer.
Line 4DECIMAL(10,0) is the numeric cast target PostgreSQL, MySQL, SQL Server and SQLite all accept; MySQL has no INTEGER target and wants SIGNED instead.
Important notes
Oracle stores the empty string as NULL, so COALESCE(city, 'unknown') returns 'unknown' there for a row that returns '' on every other engine; portable NULL handling still needs an explicit empty-string decision.
Portability costs expressiveness: the shared core has no regex, no JSON path and no date truncation. Pay that price only if you really ship on more than one engine.
Common mistakes
Treating standard as available: SUBSTRING(x FROM 2 FOR 3) and EXTRACT(YEAR FROM d) are both in the standard and both are rejected by SQL Server and SQLite, so the first deploy to those engines fails at parse time.
Porting IFNULL to ISNULL when moving MySQL code to SQL Server and back: MySQL's ISNULL takes one argument and returns 1 or 0, so the two-argument call errors there while the one-argument form returns a flag instead of the fallback value.
Using || to join strings after testing only on PostgreSQL: MySQL with default sql_mode reads it as OR and returns 0 with a warning, so the shipped report is full of zeros instead of names.
Try it yourself
Change, predict, then run
Create a table with a nullable note column, insert one row with NULL and one with an empty string, then write the 'unknown' fallback twice: once with COALESCE and once with IFNULL, ISNULL or NVL. Record which spelling your editor's engine rejects and what each returns for the empty string.
Open the SQL workspaceCheck your understanding
A reporting query developed and tested on PostgreSQL builds a label with first_name || ' ' || last_name. It is then run against MySQL with default settings. What is the most likely symptom?
- MySQL raises a syntax error on ||, so the problem is caught the first time the query runs
- MySQL reads || as logical OR, so the label column comes back 0 for almost every row and only a warning is recorded
- MySQL concatenates the values but drops the space, producing AnnLee instead of Ann Lee
- MySQL returns NULL for the label whenever either name is NULL, which PostgreSQL would not
Show answer
With the default sql_mode, PIPES_AS_CONCAT is off, so MySQL treats || as OR, converts each non-numeric string to 0, and yields 0 plus a truncation warning. The syntax-error option is tempting because it feels like invalid SQL, but MySQL parses || perfectly well and simply gives it another meaning, which is worse: a dialect difference that is legal on both sides fails silently instead of loudly.