String functions - SQL Server, Oracle, MySQL differences

stringFuncANSI SQL is a myth. There is no a single database engine that fully supports it in all aspects. Whenever I have to create a SQL query that works on more than one database engine, I have to check many pages to verify if syntax is the same in each one. Even if it is impossible to develop a truly database-agnostic application (more info can be found in database-agnostic applications article), a single query may still work.

The below table contains differences of the most popular string functions between SQL Server, Oracle and MySQL. If you have to take care of more than one database engine, you will find it useful.

 

Function Description SQL Server Oracle MySQL
ASCII(string_expression) ASCII code of a leftmost character. yes yes yes
CHR(integer_expression) Character from int ASCII code. yes yes yes and additionally supports more than one character
INSTR(string, substring) Starting position of a string in a string. CHARINDEX(find_expr, search_expr, start_pos) yes yes
CONCAT(str_expr1, str_expr2, ...) Concatenates string expressions into one. yes yes but only two string expressions are allowed yes
FORMAT(value, format) Formats a numeric or date time value. yes TO_CHAR(value, format) partially, FORMAT(value, decimal_digits)
LEFT(str_expr, num_of_chars) Left part of str_expr. yes SUBSTR(str_expr, 1, num_of_chars) yes
LEN(str_expr) Number of characters. yes (LEN vs DATALENGTH) LENGTH(str_expr) CHAR_LENGTH(str_expr)
LOWER(str_expr) Lowercase version of string. yes yes yes
LPAD(str_expr, length, pad) Pads left side of str_expr. no, but can be achieved by mixing REPLICATE, concatenation, LEN yes yes
LTRIM(str_expr) Removes leading blanks. yes yes, extended yes
REPLACE(str_expr, pattern, replacement) Replaces all occurrences of pattern yes yes yes
RPAD(str_expr, length, pad) Pads right side of str_expr. no, but can be achieved by mixing REPLICATE, concatenation, LEN yes yes
REVERSE(str_expr) Reverse ordering of characters. yes yes yes
RIGHT(str_expr, num_of_chars) Right part of str_expr. yes SUBSTR(str_expr, -num_of_chars) yes
RTRIM(str_expr) Removes trailing blanks. yes yes, extended yes
SUBSTRING(str_expr, start, length) Part of text. yes SUBSTR(str_expr, start, length) yes
UPPER(str_expr) Uppercase version of string. yes yes yes