From PostgreSQL wiki
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., ,
