Tim Cooke

String padding and concatenation in MySQL

I recently had a need to generate a specifically formatted string as part of a SQL query on MySQL. In general my SQL skills are weak so had to learn a couple of MySQL features to achieve it, which I'll present here.

The Problem

Given an Integer value, say 79, generate a string in the format P00079.

The Solution

Breaking down this problem identifies two parts:

1. Prefix with a P character
2. Left Pad the integer value with 0s up to a total of 5 characters.

After a little tour around the MySQL Documentation I found a couple of things in the String Functions and Operators page that look to be just what I'm after, specifically CONCAT() and LPAD().

Let's start with LPAD(str,len,padstr).

Returns the string str, left-padded with the string padstr to a length of len characters.

select LPAD(79, 5, '0');
'00079'

That looks good. Now let's play with CONCAT(str1,str2,...).

Returns the string that results from concatenating the arguments.

select CONCAT('P', '79');
'P79'

Also good, now to combine them to get what we really want.

select concat('P', LPAD(79, 5, '0'));
'P00079'

All done.