Query to pad left of a field with 0's

Question

I have been working on a query (in sql Server TSQL) which fills left of a number with 0's so output is always 5 digit.

So:

Select MuNumber From Mytable

for data 11,011,2132,1111

Creates output like 
00011
02134
01111

I tried Lpad Function but numer of 0's can be different.

if Munumber is 1 we need 0000 and If MyNumber is 34 we need 000

Answer 1

Assuming that MuNumber is VARCHAR simply use RIGHT

SELECT RIGHT('00000' + MuNumber, 5)
FROM Mytable

Otherwise you need to convert it first

SELECT RIGHT('00000' + CONVERT(VARCHAR(5), MuNumber), 5)
FROM Mytable

And in general you can use this pattern:

DECLARE @num INT = 10;

SELECT RIGHT(REPLICATE('0', @num) + CONVERT(VARCHAR(5), MuNumber), @num)
FROM Mytable

Answer 2

Try this

select right('00000'+cast(col as varchar(5)),5) from table

Answer 3

You can use the user defined function udfLeftSQLPadding where you can find the source codes at SQL Pad Leading Zeros

After you create the function on your database, you can use it as follows

select 
    dbo.udfLeftSQLPadding(MuNumber,5,'0')
from dbo.Mytable

Answer 4

Another option:

declare @n int = 6

select stuff(replicate('0', @n), 6-len(n), len(n), n)
from (values('123'), ('2493'), ('35')) as x(n)