[SOLVED] Zero-pad alphanumeric strings in sql

Zero-pad alphanumeric strings in sql

I would like to add zeros to an alphanumeric string if it's one digit.

What I've got is something like :

CV-1-1A
CV-1-32
CV-12-24
CV-1-2
CV-1-2A

and I would like to change it to:

CV-01-01A
CV-01-32
CV-12-24
CV-01-02
CV-01-02A

Solution

LPAD can be used to zero-pad (or pad with any character, for that matter) a string. However, it can't work inside a string, so some trickery is required. Assuming all your values have the format of a-b-c where a, b and c are strings up to 3 characters, you could use SUBSTRING_INDEX to break the string up into parts, LPAD each part individually, and use CONCAT to put them all back together:

SELECT CONCAT(SUBSTRING_INDEX(col, '-', 1), 
       '-',
       LPAD(SUBSTRING_INDEX(SUBSTRING_INDEX(col, '-', 2), '-', -1), 2, '0'), 
       '-',
       LPAD(SUBSTRING_INDEX(col, '-', -1), 3, '0'))
FROM t;

Here's an SQLFiddle with a working example.