Formatting Characters In SQL

If you want to format result values in SQL, you can use the following methods:

Let assume we have integer value variable.

DECLARE @DataValue AS int
SET @DataValue = 2

and wanna format as '00002'

There're 2 ways to format:

SELECT REPLICATE('0', 5 - LEN(@DataValue)) + CAST(@DataValue AS VARCHAR(MAX))

OR

SELECT RIGHT('00000' + CAST(@DataValue AS VARCHAR(max)), 5)