CODEWITHSUNDEEP
sql-servert-sqlsql-server-2005
vmeln
vmeln

Reputation: 1309

Format string in SQL Server 2005 from numeric value

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

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)));

Upvotes: 1

Views: 1270

Answers (2)

ErickBest
ErickBest

Reputation: 4692

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

The following illustration 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

Upvotes: 0

t-clausen.dk
t-clausen.dk

Reputation: 44316

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)

Upvotes: 2

Related Questions