String Functions and Operators Compatibility

From PostgreSQL wiki
Jump to navigationJump to search

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., ,