SQL Dialects Reference/Functions and expressions/String functions

String functions Function Since SQL Standard DB2 corefunc.html SQLite MonetDB MySQL functions-string.html#FUNCTIONS-STRING-SQL PostgreSQL Firebird Virtuoso Oracle MSSQL Linter Convert character x to ASCII r0000770.htm ASCII(x) UNICODE(x) string-functions.html#id2858110 ASCII(x) ASCII(x) ASCII(x) ASCII_VAL(x) ASCII(x) functions007.htm#i1152151 ASCII(x) 177545 ASCII(x) Convert ASCII x to character r0000778.htm CHR(x) CHAR(x) string-functions.html#id2858379 CHAR(x) CHR(x) ASCII_CHAR(x) CHR(x) functions019.htm#i76965 CHR(x) 187323 CHAR(x) CHR(x) String concatenate 92 arg1 || arg2 arg1 || arg2arg1 CONCAT arg2 arg1 || arg2 CONCAT (arg1 , arg2)arg1 || arg2 ... || argN CONCAT (multiple arguments) arg1 || arg2 arg1 || arg2 CONCAT(list) operators003.htm#i997789 arg1 || arg2functions026.htm#i77004 CONCAT (only 2 arguments)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. CONCAT(list)arg1 + arg2MS 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;arg1||arg2CONCAT(list)arg1+arg2Join items using a separator???string-functions.html#function_concat-ws CONCAT_WS(separator, item1, item2, ...)CONCAT_WS(separator, item1, item2, ...)??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) string-functions.html#id2861123 POSITION(search IN str)string-functions.html#id2859905 INSTR(str, search)string-functions.html#id2860319 LOCATE(search, str[, start]) POSITION(search IN str)STRPOS(str, search) POSITION(search IN str) SUBSTR functions068.htm#i77598 INSTR(str, search[, start]) 186323 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) INSTR functions129.htm#i1239887 REGEXP_INSTR(str, search) 188395 PATINDEX(search, str) Convert x to lowercase 92 LOWER(x) r0002411.htm LOWER(x)LCASE(x) LOWER(x) LOWER(x) string-functions.html#function_lower LOWER(x)string-functions.html#function_lcase LCASE(x) LOWER(x) LOWER(x) LCASE(x) functions081.htm#i1043828 LOWER(x) 174400 LOWER(x) LOWER(x) Convert x to uppercase 92 UPPER(x) r0000867.htm UPPER(x)UCASE(x) UPPER(x) UPPER(x) string-functions.html#function_upper UPPER(x)string-functions.html#function_ucase UCASE(x) UPPER(x) UPPER(x) UCASE(x)UPPER(x) functions206.htm#i90176 UPPER(x) 180055 UPPER(x) UPPER(x) Pad left side 2003 LPAD(str, len [, fill]) LPAD LPAD LPAD LPAD LPAD Pad right side 2003 RPAD(str, len [, fill]) RPAD RPAD RPAD RPAD RPAD Remove leading blank spaces from x 92 TRIM(LEADING [' '] FROM x) 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) 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) REPEAT REPEAT REPEAT RPAD REPEAT RPAD REPLICATE REPEAT_STRING(str, n) String of n spaces 2003 SPACE(n) SPACE(n) 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]) r0000854.htm SUBSTR(str, len[, start]) SUBSTR(str, start [, len]) SUBSTRING SUBSTRINGSUBSTR SUBSTRING(str FROM start [FOR len])SUBSTR(str, start[, len]) SUBSTRING(str FROM start [FOR len]) SUBSTR SUBSTR(str,start[,len]) 187748 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 INITCAP(x)INITCAP is supported starting DB2 V9.7. INITCAP(x) INITCAP(x) INITCAP(x) INITCAP(x) Translate string r0000862.htm TRANSLATE(str, to, from) TRANSLATE(str, from, to) functions196.htm#i1501659 TRANSLATE(str, from, to) TRANSLATE Length of string x (in characters) 92 CHAR_LENGTH(x)CHARACTER_LENGTH(x) LENGTH(x) LENGTH(x) LENGTH(x) string-functions.html#function_char-length CHAR_LENGTH(x) CHAR_LENGTH(x)CHARACTER_LENGTH(x) CHAR_LENGTH(x)CHARACTER_LENGTH(x) LENGTH(x) functions076.htm#i77725 LENGTH(x) 190329 LEN(x) LENGTH(x) Length of string x (in bytes) 92 OCTET_LENGTH(x) LENGTH(x) LENGTH(CAST(x AS BLOB)) OCTET_LENGTH(x) string-functions.html#function_length LENGTH(x) OCTET_LENGTH(x) OCTET_LENGTH(x) LENGTH(x) functions076.htm#i77725 LENGTHB(x) 173486 DATALENGTH(x) OCTET_LENGTH(x) Greatest character string in list 2003 MAX GREATEST MAX MAX GREATEST GREATEST Least character string in list 2003 MIN LEAST MIN MIN LEAST LEAST Quote SQL in string x QUOTE(x) string-functions.html#function_quote QUOTE(x) QUOTE(x) QUOTE_LITERAL(x) q'quote_delimiter x quote_delimiter' QUOTENAME(x, '''') Soundex index of string x r0000851.htm SOUNDEX(x) SOUNDEX(x)Soundex function is omitted from SQLite by default. Only available if SQLite is built with -DSQLITE_SOUNDEX=1 compile-time option. SOUNDEX(x) string-functions.html#id2861803 SOUNDEX(x)MySQL uses original Soundex algorithm. functions148.htm#i78853 SOUNDEX(x)Uses enhanced Soundex algorithm as defined in The Art of Computer Programming, Volume 3: Sorting and Searching, by Donald E. Knuth. 187384 SOUNDEX(x) Calculate MD5 hash from string x MD5(x) MD5(x) DBMS_CRYPTO.HASH (UTL_RAW.CAST_TO_RAW(X), 2) 174415 HASHBYTES('MD5', x) Calculate SHA1 hash from string x SHA1(x) DBMS_CRYPTO.HASH (UTL_RAW.CAST_TO_RAW(X), 3) 174415 HASHBYTES('SHA1', x) Generate UUID miscellaneous-functions.html#id2759047 UUID() GEN_UUID() functions168.htm#i79194 SYS_GUID() 190348 NEWID()189786 NEWSEQUENTIALID() SYS_GUID() Notes