How can you format a persisted column as a padded integer string [SQL Server 2008]

Question

What formula would I use in a persisted column so that I can add the two columns below together, the first must be padded to 5 characters:

I went for format(ID,"00000") & RefNum, but it doesn't work, any ideas please?

score 1 · Answer 1 · answered Jun 19 '12 at 15:06

You could use something like this:

ALTER TABLE dbo.YourTable
ADD FormattedColumn AS RIGHT('00000' + CAST(ID AS VARCHAR(5)), 5) + RefNum PERSISTED

Basically, convert the ID to a VARCHAR(5), prepend it with 00000 and then grab the five right-most characters.

score 0 · Accepted Answer · answered Jun 19 '12 at 15:06

0

SELECT RIGHT('00000' + CAST(ID AS VARCHAR(5)), 5) + RefNum

answered Jun 19 '12 at 15:06

Curtis

2 Answers2