Adding Leading Zeros in T-SQL

Today I ran into a situation where I had a character data type column that contained product ID numbers and these numbers were missing leading numbers. The column was a CHAR(11) as all product IDs should begin with leading zeros and be 11 characters long. To add leading zeros to a NVACHAR, VARCHAR, OR CHAR type column, use the RIGHT function.

SELECT RIGHT('00000000000' + LTRIM(RTRIM(PRODUCT_ID)),11) AS PRODUCT_ID

Or;

SELECT RIGHT(REPLICATE('0', 11) + LTRIM(RTRIM(PRODUCT_ID)),11) AS PRODUCT_ID