Jump to content

SQL Dialects Reference/Functions and expressions/String functions

Add links
From Wikibooks, open books for an open world

String functions

[edit | edit source]

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 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) N/A N/A SPACE(n) N/A RPAD SPACE(n) RPAD SPACE(n)
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

[edit | edit source]
  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.
Retrieved from "https://en.wikibooks.org/w/index.php?title=SQL_Dialects_Reference/Functions_and_expressions/String_functions&oldid=4339998"