MySQL CONCAT — a really handy function

I often use the MySQL string concatenation function concat. It is really awesome and makes my life easier. So I’ll take you through a couple of uses that I have found for it.

Making a full name out of a first and last name

CONCAT(firstname,” “,lastname) AS `full name`

Making a set of INSERT statements from the results of a SELECT

Sometimes when doing data wrangling it is easier to run a query that generates a bunch of inserts from an existing table. You can use INSERT … SELECT syntax or if you need to transfer the data to a different database server you can use a SELECT with a CONCAT line and generate the INSERT.

CONCAT(“INSERT INTO tablename (`createTime`,`numFields`) VALUES(“,quote(NOW()),”,”,numFields,”);”) AS insertSQL

Ordering by something special

Sometimes the standard options for ORDER BY don’t really cut it. You know what you want but SQL can’t do that. In that event I use a CASE statement that returns a CONCAT with a number prepended, which I then order by.