The right() function returns the right part of a character string with the specified number of characters. It starts on the right side and returns the number of characters you specify. If you string was ‘123456789’ and you specified 3, you would get 789 returned. If you specified 4, you would get 6789 returned. If you specify a bunch of zeros and concatenate a number on the right side of it you could put leading zeros on your number.

It looks like we have to use a varchar type when we use the right() function.

DECLARE @numberInt as int
DECLARE @numberVarChar as varchar(8)
DECLARE @numberChar as char(8)
SET @numberInt = 3;
SET @numberVarChar = '4';
SET @numberChar = '5';

SELECT cast('H2345' as char(5))
UNION ALL
/* Leading zeros are required */
SELECT right('00000000' + @numberint,8)   /* no */
UNION ALL
SELECT right('00000000' + @numberVarChar,8)
UNION ALL
SELECT right('00000000' + @numberChar,8)  /* no */
UNION ALL
SELECT right('00000000' + cast(@numberint as varchar(8)),8)
UNION ALL
SELECT right('00000000' + cast(@numberChar as varchar(8)),8) /* no */
UNION ALL
SELECT cast('T' as char(1))

Here is the output from SQL Server in text mode.

DECLARE @numberInt as int
DECLARE @numberVarChar as varchar(8)
/* we have an integer that needs leading zeros */
SET @numberInt = 3;
SET @numberVarChar = cast(@numberInt as varchar(8))

SELECT cast('H2345' as char(5))
UNION ALL
/* Leading zeros are required */
SELECT right('00000000' + @numberVarChar,8)
UNION ALL
SELECT right('00000000' + cast(@numberint as varchar(8)),8)
UNION ALL
SELECT cast((right('00000000' + @numberVarChar,8)) as char(8))
UNION ALL
SELECT cast('T' as char(1))

Here is the output. Also notice the lack of spaces after the T line.

SET @numberInt = 3;
SET @numberVarChar = cast(@numberInt as varchar(8))

SELECT cast('H2345' as char(5))
UNION ALL
/* Leading zeros are required */
SELECT cast(right('00000000' + @numberVarChar,8) as char(8))
	+ cast('abc' as char(3))
	+ cast(left(cast('Myname' as varchar(8)) + '        ',8) as char(8))
	+ cast('000' as char(3))
UNION ALL
SELECT cast('T' as char(1))

Below is the output. Notice that our T line trailing spaces are back. Why?