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.
Given an Integer value, say
79, generate a string in the format
Breaking down this problem identifies two parts:
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
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
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'