# 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) | CONCAT(list) arg1 + arg2 | 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) | 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) | SOUNDEX(x) | SOUNDEX(x) | N/A | N/A | N/A | SOUNDEX(x) | 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

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