SQL Server Set Digit

How to set total digit in SQL Server 2008. For example, I set 5 as the total digit of an ITEM ID. So that if the actual ITEM ID i开发者_如何转开发s 205, it will be converted and shown as 00205. Thx !

Generally, you should pad the data after it comes back from the database, not in the database itself.

If you must, though:

SELECT RIGHT('00000' + CAST(column_name AS VARCHAR(5)), 5) AS my_column FROM table_name

select 
  REPLICATE('0', 5 - len(cast(id as varchar)))+cast(id as varchar) as str_id
from 
  mytable