MySQL concatenation and casting
I recently set up my environment for working through SQL for Mere Mortals.
I’ll record some interested tidbits here as I go.
Chapter 5: Concatenation without the || operator
I use MySQL at work, and MySQL doesn’t support the
|| operator for string concatenation.
So, in the book, an expression like:
ItemOne || ItemTwo
|| is a logical operator, just like in Ruby, so to get the above evaluation, you’d need to use:
That would give you
ItemOneItemTwo. Of course, string concatenation pairs well with spaces between the strings you’re trying to concatenate, so please know that the
CONCAT function can take any number of arguments, for example:
CONCAT(ItemOne, ' ', ItemTwo)
On page 147, the given SQL doesn’t work in MySQL:
SELECT EngagementNumber, CAST(CAST(EndDate - StartDate AS INTEGER) + 1 AS CHARACTER) || ' day(s)' AS DueToRun FROM Engagements
There’s two problems. First, the concatenation issue mentioned above, so it would look like this:
SELECT EngagementNumber, CONCAT(CAST(CAST(EndDate - StartDate AS INTEGER) + 1 AS CHARACTER), ' day(s)') AS DueToRun FROM Engagements
CAST function doesn’t play as expected.
Here’s a functioning query:
SELECT EngagementNumber, EndDate, StartDate, CONCAT(EndDate + 1 - StartDate, ' Day(s)') AS DaysToRun FROM engagements
I plan on just replacing any use of
|| in the book with the
CONCAT function, unless someone suggests otherwise.
To enable it in the current MySQL session, in the query interface just run
Now you can try the pipes operator, and decide if you want it to be permanent or not.
This is pretty basic stuff. Chapter 6 was just about using
IS NULL and
NOT LIKE clauses to filter down result sets.
I rushed through it to get to the more interesting portion of the book, which is Chapter 7: Thinking In Sets (AKA Joins tables!)