Function Since SQL Standard DB2 SQLite MonetDB MySQL PostgreSQL Firebird Virtuoso Oracle MSSQL Linter
Convert character x to ASCII N/A N/A ASCII(x) UNICODE(x) ASCII(x) ASCII(x) ASCII(x) ASCII_VAL(x) ASCII(x) ASCII(x) ASCII(x) N/A
Convert ASCII x to character N/A N/A CHR(x) CHAR(x) N/A CHAR(x) CHR(x) ASCII_CHAR(x) CHR(x) CHR(x) CHAR(x) CHR(x)
String concatenate 92 arg1 || arg2 arg1 || arg2
arg1 CONCAT arg2		 arg1 || arg2 CONCAT (arg1 , arg2)
arg1 || arg2 ... || argN		 CONCAT (multiple arguments) arg1 || arg2 arg1 || arg2 CONCAT(list) arg1 || arg2
CONCAT (only 2 arguments)[1]		 CONCAT(list)
arg1 + arg2[2]		 arg1||arg2
CONCAT(list)
arg1+arg2
Join items using a separator N/A N/A ? ? ? CONCAT_WS(separator, item1, item2, ...) CONCAT_WS(separator, item1, item2, ...) ? ? N/A CONCAT_WS(separator, item1, item2, ...) ?
Find first occurrence of substring search in str, starting from start 92 POSITION(search IN str) LOCATE(search, str[, start])
POSSTR(str, search)		 INSTR(str, search) POSITION(search IN str) POSITION(search IN str)
INSTR(str, search)
LOCATE(search, str[, start])		 POSITION(search IN str)
STRPOS(str, search)		 POSITION(search IN str) SUBSTR INSTR(str, search[, start]) CHARINDEX(search, str[, start]) POSITION(search IN str)
INSTR(str, search, [start [,[n]])
Find first occurrence of pattern search in string str 2003 SUBSTRING_REGEX (search IN str)   N/A N/A   N/A N/A INSTR REGEXP_INSTR(str, search) PATINDEX(search, str) N/A
Convert x to lowercase 92 LOWER(x) LOWER(x)
LCASE(x)		 LOWER(x) LOWER(x) LOWER(x)
LCASE(x)		 LOWER(x) LOWER(x) LCASE(x) LOWER(x) LOWER(x) LOWER(x)
Convert x to uppercase 92 UPPER(x) UPPER(x)
UCASE(x)		 UPPER(x) UPPER(x) UPPER(x)
UCASE(x)		 UPPER(x) UPPER(x) UCASE(x)
UPPER(x)		 UPPER(x) UPPER(x) UPPER(x)
Pad left side 2003     N/A LPAD(str, len [, fill]) LPAD LPAD LPAD N/A LPAD N/A LPAD
Pad right side 2003     N/A RPAD(str, len [, fill]) RPAD RPAD RPAD N/A RPAD N/A RPAD
Remove leading blank spaces from x 92 TRIM(LEADING [' '] FROM x) N/A LTRIM(x) LTRIM(str [, str]) LTRIM LTRIM TRIM(LEADING [' '] FROM x) LTRIM LTRIM LTRIM LTRIM
Remove trailing blank spaces from x 92 TRIM(TRAILING [' '] FROM x) N/A RTRIM(x) RTRIM(str [, str]) RTRIM RTRIM TRIM(TRAILING [' '] FROM x) RTRIM RTRIM RTRIM RTRIM
Remove leading and trailing blanks from x 92 TRIM(BOTH [' '] FROM x)
TRIM(x)		 LTRIM(RTRIM(x))
or TRIM(x)		 TRIM(x) TRIM(str [, str]) TRIM TRIM TRIM(BOTH [' '] FROM x)
TRIM(x)		 TRIM TRIM LTRIM(RTRIM(x)) TRIM
Repeat str n times 2003   REPEAT(str, n) N/A REPEAT REPEAT REPEAT RPAD REPEAT RPAD REPLICATE REPEAT_STRING(str, n)
String of n spaces 2003   SPACE否 N/A N/A SPACE否 N/A RPAD SPACE否 RPAD SPACE否  
Convert number to string 2003   CHAR(num) CAST CAST   CAST CAST   TO_CHAR STR TO_CHAR
Substring from string str, starting from start, length of len 92 SUBSTRING(str FROM start [FOR len]) SUBSTR(str, len[, start]) SUBSTR(str, start [, len]) SUBSTRING SUBSTRING
SUBSTR		 SUBSTRING(str FROM start [FOR len])
SUBSTR(str, start[, len])		 SUBSTRING(str FROM start [FOR len]) SUBSTR SUBSTR(str,start[,len]) SUBSTRING(str, start, length) SUBSTRING(str,start[,len])
SUBSTRING(str FROM start [FOR len])
The same with SUBSTR instead of SUBSTRING
Replace characters     REPLACE(string, from, to) REPLACE(str, from, to) REPLACE REPLACE REPLACE REPLACE(str, find, repl) REPACE REPLACE REPLACE REPLACE
Capitalize first letter of each word in string x N/A N/A INITCAP(x)[3] N/A N/A N/A INITCAP(x) N/A INITCAP(x) INITCAP(x) N/A INITCAP(x)
Translate string     TRANSLATE(str, to, from) N/A N/A N/A TRANSLATE(str, from, to) N/A   TRANSLATE(str, from, to) N/A TRANSLATE
Length of string x (in characters) 92 CHAR_LENGTH(x)
CHARACTER_LENGTH(x)		 LENGTH(x) LENGTH(x) LENGTH(x) CHAR_LENGTH(x) CHAR_LENGTH(x)
CHARACTER_LENGTH(x)		 CHAR_LENGTH(x)
CHARACTER_LENGTH(x)		 LENGTH(x) LENGTH(x) LEN(x) LENGTH(x)
Length of string x (in bytes) 92 OCTET_LENGTH(x) LENGTH(x) LENGTH(CAST(x AS BLOB)) OCTET_LENGTH(x) LENGTH(x) OCTET_LENGTH(x) OCTET_LENGTH(x) LENGTH(x) LENGTHB(x) DATALENGTH(x) OCTET_LENGTH(x)
Greatest character string in list 2003     MAX     GREATEST MAX MAX GREATEST N/A GREATEST
Least character string in list 2003     MIN     LEAST MIN MIN LEAST N/A LEAST
Quote SQL in string x       QUOTE(x)   QUOTE(x) QUOTE(x) N/A QUOTE_LITERAL(x) q'quote_delimiter x quote_delimiter' QUOTENAME(x, '''')
Soundex index of string x     SOUNDEX(x) SOUNDEX(x)[4] SOUNDEX(x) SOUNDEX(x)[5] N/A N/A N/A SOUNDEX(x)[6] SOUNDEX(x) N/A
Calculate MD5 hash from string x N/A N/A   N/A   MD5(x) MD5(x) N/A N/A DBMS_CRYPTO.HASH (UTL_RAW.CAST_TO_RAW(X), 2) HASHBYTES('MD5', x) N/A
Calculate SHA1 hash from string x N/A N/A   N/A   SHA1(x) N/A N/A N/A DBMS_CRYPTO.HASH (UTL_RAW.CAST_TO_RAW(X), 3) HASHBYTES('SHA1', x) N/A
Generate UUID N/A N/A   N/A   UUID()   GEN_UUID()   SYS_GUID() NEWID()
NEWSEQUENTIALID()		 SYS_GUID()

注释说明（Notes）

  1. Oracle 的字符串连接操作不会被 NULL 值“吞噬”（即不会因为有 NULL 而导致整个结果变为 NULL），这点与 ANSI SQL 标准不同。不过，Oracle 官方文档警告：这种行为未来可能会发生改变，因此建议开发者显式地使用 COALESCE 来处理 NULL 值。

  2. Microsoft SQL Server（MS SQL） 的 CONCAT() 函数结果也不会因 NULL 而失效（与 ANSI SQL 不同）。当使用 + 运算符连接字符串时，如果设置 SET CONCAT_NULL_YIELDS_NULL OFF，连接同样不会被 NULL 吞噬。

  3. DB2 从 V9.7 开始支持 INITCAP 函数（将字符串的首字母大写，其余小写）。

  4. SQLite 默认不包含 Soundex 函数。仅在使用 -DSQLITE_SOUNDEX=1 编译选项构建时才可用。

  5. MySQL 使用的是最初的（原始）Soundex 算法。

  6. 某些实现使用改进版的 Soundex 算法，该算法定义于 Donald E. Knuth 的《计算机程序设计艺术》第三卷《排序与查找》中。

Notes

  1. ↑ Oracle concatenation result does not get "eaten" by NULLs (unlike in ANSI SQL). Oracle documentation warns about potential future change in this behavior and recommends explicitly coalescing NULLs.
  2. ↑ MS SQL concat() result does not get "eaten" by NULLs (unlike in ANSI SQL). + result does not get "eaten" when SET CONCAT_NULL_YIELDS_NULL OFF;
  3. ↑ INITCAP is supported starting DB2 V9.7.
  4. ↑ Soundex function is omitted from SQLite by default. Only available if SQLite is built with -DSQLITE_SOUNDEX=1 compile-time option.
  5. ↑ MySQL uses original Soundex algorithm.
  6. ↑ Uses enhanced Soundex algorithm as defined in The Art of Computer Programming, Volume 3: Sorting and Searching, by Donald E. Knuth.
最后修改: 2025年04月11日 星期五 11:17