Concatentating Strings in SQLITE

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:

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.

DBMSHow they concatenate
IngresCONCAT, + or ||.
MariaDB CONCAT or by placing strings together: ‘hello’ ‘ there’.
Microsoft Access &.
MySQL CONCAT and || when in PIPES_AS_CONCAT mode.
Postgres CONCAT plus ||.
OracleCONCAT or ||.
SQLITE ||.
SQL ServerCONCAT or +.
twitterredditpinteresttumblrmail

Written by Stephen Moon
email: stephen at logicalmoon.com
www: https://www.logicalmoon.com


Hey! Did you enjoy reading this? If you did and would like an email when I add new content, just subscribe to my list. You can unsubscribe at any time.


Leave a Reply

Your email address will not be published. Required fields are marked *