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.