ANSI 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 |
