SQL Dialects Reference/Functions and expressions/String functions

String functions

This page includes comparison tables which can be big and complex.

While it's perfectly all right to view them in their complete state, it is recommended to install a comparison tables extension that would allow to select particular columns of interest for comparison.

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 side2003N/ALPAD(str, len [, fill])LPADLPADLPADN/ALPADN/ALPAD
Pad right side2003N/ARPAD(str, len [, fill])RPADRPADRPADN/ARPADN/ARPAD
Remove leading blank spaces from x92TRIM(LEADING [' '] FROM x)N/ALTRIM(x)LTRIM(str [, str])LTRIMLTRIMTRIM(LEADING [' '] FROM x)LTRIMLTRIMLTRIMLTRIM
Remove trailing blank spaces from x92TRIM(TRAILING [' '] FROM x)N/ARTRIM(x)RTRIM(str [, str])RTRIMRTRIMTRIM(TRAILING [' '] FROM x)RTRIMRTRIMRTRIMRTRIM
Remove leading and trailing blanks from x92TRIM(BOTH [' '] FROM x)
TRIM(x)		LTRIM(RTRIM(x))
or TRIM(x)		TRIM(x)TRIM(str [, str])TRIMTRIMTRIM(BOTH [' '] FROM x)
TRIM(x)		TRIMTRIMLTRIM(RTRIM(x))TRIM
Repeat str n times2003REPEAT(str, n)N/AREPEATREPEATREPEATRPADREPEATRPADREPLICATEREPEAT_STRING(str, n)
String of n spaces2003SPACE(n)N/AN/ASPACE(n)N/ARPADSPACE(n)RPADSPACE(n)
Convert number to string2003CHAR(num)CASTCASTCASTCASTTO_CHARSTRTO_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 charactersREPLACE(string, from, to)REPLACE(str, from, to)REPLACEREPLACEREPLACEREPLACE(str, find, repl)REPACEREPLACEREPLACEREPLACE
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 list2003MAXGREATESTMAXMAXGREATESTN/AGREATEST
Least character string in list2003MINLEASTMINMINLEASTN/ALEAST
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 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.
Category:Book:SQL Dialects Reference#Functions%20and%20expressions/String%20functions%20
Category:Book:SQL Dialects Reference