How to concatenate two strings in sql and pad one string with 0's?

Question

I have two fields in a table. One contains values such as BTA, BEA, REA. The other contains values such as 1,2,63,103.

I want to combine the 2 fields so they look like this BTA001, BTA002, BTA063, BTA103.

Notice how if the numbers are not 3 characters in length, I want to pad some 0's to the left of the number to make it equal to 3.

How would I go about doing this? The fields in the table are called Type which correspond to BTA, BEA, and REA and Id is the field that corresponds to 1, 2, 63, and 103.

Answer 1

select Type + right('00' + cast(id as varchar(10)), 3)
from ...

Edit: if id can be null and you would like a zero to show, you can do:

select Type + right('00' + cast(isnull(id, 0) as varchar(10)), 3) 
from ...

Answer 2

select C1 + right(('000' + cast(C2 as nvarchar(10)),3) as

from t1

Answer 3

select FIELD1 + RIGHT('000' + CONVERT(VARCHAR,FIELD2), 3)