SQLite has been my life as of late and because of this I’m discovering of a number of tricks. The one I’ll reveal today, once again makes absolutely no sense at all. (Barney shed some light on my previous find, so it wasn’t as astonishing)
Let’s say we have a database of people, and in this database, we separate names into first and last names—this comes in handy for sorting by last name, etc. In our case, we want to retrieve the names into a combined-form, such as name=Jonnie Hallman instead of firstname=Jonnie, lastname=Hallman. This could easily be done, after the fact, in Actionscript, but we want to optimize the process. SQL wizard, James Hall, suggests the following query:
// Correct in SQL // Incorrect in SQLite
SELECT (firstname + ” ” + lastname) AS fullname FROM tablename;
It doesn’t receive an error in SQLite, but it does return a value of zero for that column. In SQL it works though. Here’s the query that works in SQLite:
// Correct in SQLite
SELECT (firstname || ” ” || lastname) AS fullname FROM tablename;
Stunned?—I am. Why would the logical “OR” equivalent be used to combine two strings? If you have the answer to the insanity, please enlighten me. If not, enjoy the tip!
[update] Thanks for the correction on the logical “OR,” not bitwise.