Padding text and numbers in SQL SERVER 2000

(Recently I had a job to search for mapping between 5 tables. There was no documentation and no FKs between them. So I had to do it the hard way out.
As soon as I began I found two different columns in two different tables, which looked to be same. Well both of them were customer numbers. But one of them was had a width of 8 characters while the other was embedded as Customer number alongwith two other fields.
Moreover the length customer number field in the second column was only 6. Well 'That would be an easy job of padding I thought' and used 'lpad' which gave me an error instantly.
Ohh yes I know what you think, just append '00' to that, smart UHH!. But heres the twist, some of the numbers were just 4 or 5 digits long in it. And the corresponding column had exactly eight digits in every row. )


You could as well skip that short story of mine above.
Well I could not find any function defined for SQL SERVER which would do left padding for me, But did get my hands on some forums that gave me the folowing Idea.

Consider a six digit cust_num say '103875', And I have to convert it to say '00103875'. well just use

SELECT RIGHT( REPLICATE('0',8)+'103875' , 8)

or rather

SELECT RIGHT( REPLICATE('0',8)+cust_num , 8) from table1

My job also included some playing with substrings as i had to cut the number from another number. But the above query would explain everything about padding.

Somethings I would like to mention about the above query are the function Replicate(), actually replicates the string parameter (1st) , (2nd) parameter number of times. I.e. '0' eight times in this case.
And RIGHT function gives you the n number of characters from the string. again 8 in this condition.

So even if the number would have been '98745' the following would have taken place

Replicate + '98745' --> '00000000'+'98745'

Right(,8)
'0000000098745' --> '00098745'

Ya I know it looks much more like the CHEMICAL formulae we use to write in school. BUT I hope I made my point.


Now to append something to numbers lets say 3 zeroes on number 6 on the left we use the following...


select right(convert(varchar , 10000+6 ),4)


I don't think this needs to be explained. And yes this would append zeroes on the left but what if we want zeroes on right... I know that must be easy now.. Just use '*' instead of '+' and 'left' instead of 'right'.

FUNCTIONS USED : Convert , Right , Replicate