I have a table in which I have an auto-increment int ID column. Can I set this column to pad the zeroes at the beginning so as to store them as, say, 0001 instead of 1, 0002 instead of 2, and so on?
Subsequently, when the table reaches 10 entries, the ID should be 0010; on 100, it becomes 0100. Implying that the number of zeroes to be padded does not always remain 3.
The reason I wanna do this is because this auto-increment column is not the primary key column. Based on these integers, another column (which is the key) is to be updated such that the character triad of 'MLS' is concatenated to the int IDs like this:
ID MLSNumber
0001 MLS0001
0002 MLS0002
0003 MLS0003
. .
. .
. .
etc.
I am trying to setup a trigger for this concatenation process, but was wondering if there is an in-built way in SQL 2005 Express to pad the numbers rather than me having to do it. If not, what's the best way to proceed?