Josh Thompson     about     archive     tags

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

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!)

Get occasional emails

If I've written any new posts, you'll get an email with summaries on Friday. If I've not, you'll not hear from me.