MS SQL Return record count with leading zeros

  • Published by steve on 5 January 2012.

First I set the column of REC to a datatype of bigint . Then i run this query to do a count of records:

DECLARE @count bigint
SET @count = 0
UPDATE (table name) SET @count = rec = @count + 1

This should fill the whole row starting at 1 and counting up by 1 each record.


Then I went into the Design Table routine from Enterprise Manager and changed the REC column to a datatype of CHAR(15).

Closed Enterprise manager and went back into Query Analyzer and entered this query:

update (table name) set rec = REPLACE(STR(rec,15), SPACE(1), '0')

This replaces the emtpy spaces with a 0 to pad the field with leading zeros. eg. 1 turns into 000000000000001, 3456 turns into 000000000003456