Format string in SQL Server 2005 from numeric value

Question

How I can format string with D in start and leading zeros for digits with length of less than four. E.g:

• D1000 for 1000
• D0100 for 100

I have tried to work with casting and stuff function, but it didn't work as I expected.

SELECT STUFF('D0000', LEN(@OperatingEndProc) - 2, 4, CAST((CAST(SUBSTRING(@OperatingEndProc, 2, 4) AS INT) + 1) AS VARCHAR(10)));

Answer 1

adding 10000 to the value will cause the number to have have extra zeros first, then casting it as varchar and only using the last 4 will ignore the added 10000. This require that all numbers are between 0 and 9999

declare @value int = 100

select 'D' + right(cast(@value + 10000 as varchar(5)), 4)

Answer 2

This illustration board can come in handy when you wanna get the proper casting practices..

This shows all explicit and implicit data type conversions that are allowed for SQL Server system-supplied data types. These include xml, bigint, and sql_variant. There is no implicit conversion on assignment from the sql_variant data type, but there is implicit conversion to sql_variant

You can download it here http://www.microsoft.com/en-us/download/details.aspx?id=35834