MySQL concatenation and casting

January 2018

Reading time: 2 mins

Article Table of Contents

Chapter 5: Concatenation without the || operator
CASTing
Enable pipes_as_concat mode?
Summary

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

evaluates to ItemOneItemTwo

In MySQL, || is a logical operator, just like in Ruby, so to get the above evaluation, you’d need to use:

CONCAT(ItemOne, ItemTwo)

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)

would return ItemOne ItemTwo

CASTing #

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

Secondly, the 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

¯\_(ツ)_/¯

Enable `pipes_as_concat` mode? #

I plan on just replacing any use of || in the book with the CONCAT function, unless someone suggests otherwise.

You can run MySQL in pipes_as_concat mode, according to StackOverflow.

To enable it in the current MySQL session, in the query interface just run

set sql_mode=PIPES_AS_CONCAT;

Now you can try the pipes operator, and decide if you want it to be permanent or not.

Summary #

This is pretty basic stuff. Chapter 6 was just about using WHERE and 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!)