Formatting Integer | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Formatting Integer

Is there any function which in SQL which returns the integer in a given format as we use to do in a front end application for eg.format(2,"000") will give me 002 and if 22 is given the result will be 022…….
can any one provide me solution for this… Mat
declare @i int
Set @i = 22
declare @c as varchar(15)
set @c = cast(@i as varchar(15))
select replicate(‘0’,3-len(@c)) + @c —————————————-
Contributing Editor, Writer & Forums Moderator
http://www.SQL-Server-Performance.Com Visit my Blog at

Another way would be:
SELECT REPLACE(STR(@i, 3), ‘ ‘, ‘0’)

Frank Kalis
Microsoft SQL Server MVP
Thanks for the reply guys but if the value i’am passing is greater than 3 digits both solution will not work….in this case maybe i need to write a UDF, but apart from UDF any other solution is there to meet if any number passed….
Thanks in advance Mat
I think in both cases, you should be able to replace the 3 by a variable. —
Frank Kalis
Microsoft SQL Server MVP