T-SQL: Padding Values

Sometimes its the simple things that really irritate you as a DBA. Case in point is the lack of a LPAD, RPAD functionality in T-SQL. What makes it slightly more inconvenient is that there is similarly no LEFT and RIGHT functions to compliment the SUBSTRING function.

These string functions often come in handy when integrating values between different systems. You might come across situations in which different systems may be referring to possibly the same account number but in a slightly different way such as one being padded with zeros.

System #1:  0012345

System #2: 12345

Additionally, in integration project it becomes necessary to handle null values. A lot of the time it is not necessarily that there is no data but maybe a key data field is left unpopulated …such as an SSN. For these generally, you would want to replace the NULL value with something more significant like UNKXXXXXX. Without a good LPAD function it becomes annoying to left pad the number in the above case with zeros.

So how do you do it? Well, one method that I like to use involves using the REPLICATE function. The REPLICATE function takes a character value and simply copies it N number of times. The syntax is below..

REPLICATE(‘<character value>’, <number of times to copy>)

So if we were to want to pad out account number example above to say 10 characters then our query  would simply look like this:

SELECT

REPLICATE(‘0’, 10-LEN(account_number))+account_number as padded_acct

FROM ACCOUNTS;

Simple enough, now everything is good to go for matching these values up.

Cheers,
AJ