I’ve been using a SQLITE database in my current project (mostly because it is just so easy) and all went well until I needed to concatenate two strings from the database. “I know!”, I thought. “I will just use the SQL Standard function, ‘CONCAT‘ – it shouldn’t take more than a minute,” and didn’t give it a second thought.
But I was wrong! SQLITE doesn’t support that function.
If you want to concatentate two strings using the SQL syntax in SQLITE, you need to use the double pipe operator like this:
SELECT COLUMN1 || COLUMN2
That got me wondering, though. Are there any other databases that act in a non-standard way? A quick Google brought me to the font of all knowledge: Wikipedia.
SQL implementations are incompatible between vendors and do not necessarily completely follow standards. In particular date and time syntax, string concatenation, NULLs, and comparison case sensitivity vary from vendor to vendor.
So, to save you a bit of searching, I looked up how some other popular DBMS’ concatenate strings.
|DBMS||How they concatenate|
|Ingres||CONCAT, + or ||.|
|MariaDB||CONCAT or by placing strings together: ‘hello’ ‘ there’.|
|MySQL||CONCAT and || when in PIPES_AS_CONCAT mode.|
|Postgres||CONCAT plus ||.|
|Oracle||CONCAT or ||.|
|SQL Server||CONCAT or +.|
Written by Stephen Moon
email: stephen at logicalmoon.com