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
We use cookies

We use cookies on our website. Some of them are essential for the operation of the site, while others help us to improve this site and the user experience (tracking cookies). You can decide for yourself whether you want to allow cookies or not. Please note that if you reject them, you may not be able to use all the functionalities of the site.