SQL
SyntaxFunctionsData types
compatibility table
Fork
Compatible Compatible with warning Not compatible Unknown
| Function | MySQL MariaDB |
Oracle Database |
PostgreSQL | Microsoft SQL Server |
IBM DB2 | SQLite | Other database systems |
ANSI/ISO standard |
Universal alternative | |
|---|---|---|---|---|---|---|---|---|---|---|
| AVG(x) | Yes | Yes | Yes | Yes | Yes | Yes | Access Firebird Interbase | Sybase Informix CUBRID | SQL-92 | |
| COALESCE(x,y...) | Yes | Yes | Yes | Yes | Yes | Yes | Access Firebird Interbase Sybase CUBRID | Informix: NVL(x,y) | SQL-92 | |
| COUNT(x) | Yes | Yes | Yes | Yes | Yes | Yes | Microsoft Access | and all others... | SQL-92 | |
| MAX(x) | Yes | Yes | Yes | Yes | Yes | Yes | Access Firebird Interbase | Sybase Informix CUBRID | SQL-92 | |
| MIN(x) | Yes | Yes | Yes | Yes | Yes | Yes | Access Firebird Interbase | Sybase Informix CUBRID | SQL-92 | |
| NULLIF(x,y) | Yes | Yes | Yes | Yes | Yes | Yes | Firebird | CUBRID | SQL-92 | |
| REPLACE(x,y,z) | Yes | Yes | Yes | Yes | Yes | Yes | Microsoft Access | |||
| SUM(x) | Yes | Yes | Yes | Yes | Yes | Yes | Access Firebird Interbase | Sybase Informix CUBRID | SQL-92 | |
| Other functions | ||||||||||
| CHAR_LENGTH(x) | Yes | No | Yes | No | ? | No | Firebird | SQL-92 | see notes | |
| CHARACTER_LENGTH(x) | Yes | No | ? | No | ? | No | SQL-92 | see notes | ||
| IFNULL(x,y) | Yes | No | v6.5 only | No | ? | Yes | CUBRID | Microsoft Access: NZ(x,y) | COALESCE(x,y) | |
| ISNULL(x) | Yes | No | No | ISNULL(x,y) | ? | No | CUBRID | ... WHERE x IS NULL | ||
| LEFT(x,y) | Yes | No | Yes | Yes | ? | No | Firebird | |||
| LEN(x) | No | No | No | Yes | ? | No | Microsoft Access | see notes | ||
| LENGTH(x) | Yes | Yes | Yes | No | Yes | Yes | Firebird | Microsoft Access | see notes | |
| NVL(x,y) | No | Yes | No | No | Yes | No | Access Firebird Interbase Sybase | Informix & CUBRID | ||
| NVL2(x,y,z) | No | Yes | No | No | No | No | Access Firebird Interbase Sybase | Informix & CUBRID | ||
| RIGHT(x,y) | Yes | No | Yes | Yes | ? | No | Firebird | |||
| SUBSTR(x,y,z) | Yes | Yes | Yes | No | Yes | Yes | Firebird | |||
| SUBSTRING(x,y,z) | Yes | No | No | param z required | Yes | No | Firebird | |||
| SUBSTRING(x FROM y FOR z) | Yes | No | Yes | No | Yes | No | Firebird | SQL-92 | ||
Notes
- It's possible to create stored procedures for unavailable functions in your database system (except with SQLite)
-
Some functions used with WHERE can be replaced by the keyword LIKE:
...WHERE SUBSTR(col,0,3) = 'abc' can be replaced by ...WHERE col LIKE 'abc%'
...WHERE LENGTH(col) = 3 can be replaced by ...WHERE col LIKE '___'
Reference manuals
SQL standard publications