SQL Dialects Reference/Functions and expressions/String functions

From Wikibooks, open books for an open world
Jump to: navigation, search

String functions[edit]

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) N/A 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) N/A 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)
arg1 + arg2 arg1||arg2
CONCAT(list)
arg1+arg2
Find first occurrence of substring search in str, starting from start 92 SUBSTRING_REGEX (search IN str FROM start) LOCATE(search, str[, start])
POSSTR(str, search)
N/A 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)
N/A 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 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 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) N/A 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)[1] 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(x) 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) N/A QUOTE_LITERAL(x) q'quote_delimiter x quote_delimiter' QUOTENAME(x, '''')
Soundex index of string x SOUNDEX(x) SOUNDEX(x)[2] SOUNDEX(x) SOUNDEX(x)[3] N/A N/A N/A SOUNDEX(x)[4] 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 UUID() GEN_UUID() SYS_GUID() NEWID()
NEWSEQUENTIALID()
SYS_GUID()

Notes[edit]

  1. INITCAP is supported starting DB2 V9.7.
  2. Soundex function is omitted from SQLite by default. Only available if SQLite is built with -DSQLITE_SOUNDEX=1 compile-time option.
  3. MySQL uses original Soundex algorithm.
  4. Uses enhanced Soundex algorithm as defined in The Art of Computer Programming, Volume 3: Sorting and Searching, by Donald E. Knuth.