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:

SELECT COLUMN1 || COLUMN2
FROM X;

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.

---

Hi! Did you find this useful or interesting? I have an email list coming soon, but in the meantime, if you ready anything you fancy chatting about, I would love to hear from you. You can contact me here or at stephen ‘at’ logicalmoon.com