Need An LPAD Type Function

Hello all, dumb question here I'm sure.

I have social security numbers stored in an INT data type column. Leading zeros keep getting dropped.

I am executing a stored procedure to export the data into other tables and fixed length text files, and when I do, the leading zeros drop.

Example

SSNO = 11111

I want this 000011111

But I get the 11111


I've used this command.

select (REPLICATE(0, (9 - len(11111))) + '11111')

Which works fine.

But! When I substituted @v_ssno for the literal string, It truncates the leading zeros!

select (REPLICATE(0, (9 - len(@v_ssno))) + @v_ssno)


So I have tried CAST to make it a VARCHAR, hopeing that it would not clip the zeros. But it still does every time I put it in a variable holder.


Oracle has a command called LPAD. Basically you tell it the total length of the field, and the character to "pad" on the left with.


I can't for the life of me find the equivalent in MS SQL 2000.


Any information is massively appreciated!

Thanks.