Various databases have various standard and non-standard string functions and operators, but they often don't explain enough in the edge cases. This page shows the compatibility of those operations.
Functions
concat
| Feature
|
code
|
PostgreSQL 9.1
|
MySQL
|
Oracle
|
DB2
|
| non-text args
|
concat('a', 123)
|
a123
|
a123
|
a123
|
a123
|
| NULL args
|
concat('a', NULL)
|
a
|
NULL
|
a
|
a
|
| 3+ args
|
concat('a', 'b', 'c')
|
abc
|
abc
|
(not supported)
|
(not supported)
|
concat_ws
'ws' means 'with separator'.
| Feature
|
code
|
PostgreSQL 9.1
|
MySQL
|
| non-text args
|
concat_ws(',', 'a', 123)
|
a,123
|
a,123
|
| NULL args
|
concat_ws(',', 'a', NULL)
|
a
|
a
|
| 3+ args
|
concat_ws(',', 'a', 'b', 'c')
|
a,b,c
|
a,b,c
|
| NULL separator
|
concat_ws(NULL, 'a', 'b')
|
NULL
|
NULL
|
left
| Feature
|
code
|
PostgreSQL 9.1
|
MySQL
|
SQL Server
|
DB2
|
| positive length
|
left('abcde', 2)
|
ab
|
ab
|
ab
|
ab
|
| zero length
|
left('abcde', 0)
|
(empty)
|
(empty)
|
(empty)
|
(empty)
|
| negative length
|
left('abcde', -2)
|
abc
|
(empty)
|
ERROR
|
ERROR
|
- Negative length in PostgreSQL: left(str, -len) = left(str, length(str) - len)
right
| Feature
|
code
|
PostgreSQL 9.1
|
MySQL
|
SQL Server
|
DB2
|
| positive length
|
right('abcde', 2)
|
de
|
de
|
de
|
de
|
| zero length
|
right('abcde', 0)
|
(empty)
|
(empty)
|
(empty)
|
(empty)
|
| negative length
|
right('abcde', -2)
|
cde
|
(empty)
|
ERROR
|
ERROR
|
- Negative length in PostgreSQL: right(str, -len) = right(str, length(str) - len)
reverse
| Feature
|
code
|
PostgreSQL 9.1
|
MySQL
|
SQL Server
|
| multibyte safe?
|
reverse('...')
|
Yes
|
Yes
|
Yes
|
Operators
||
| Feature
|
code
|
PostgreSQL
|
Oracle
|
DB2
|
SQL Server (+)
|
| non-text args
|
'a' || 123
|
a123
|
a123
|
a123
|
ERROR
|
| NULL args
|
'a' || NULL
|
NULL
|
a
|
NULL
|
NULL
|
- MySQL doesn't support || operator to join strings. Use concat() instead. (|| means OR in MySQL.)
- SQL Server doesn't support || operator to join strings. Use + instead., ,
