How to add leading zeros to a auto numbering ID

Question

I need to add a "TransactionID" to a file - it will be a number that uniquely identifies each record. Must be all numeric characters, use leading zeros to complete the field. The data type is text with max length of 12.

So a 1 would be "000000000001" and if the number is 1657 then it should print "00000001657".

I did this but the TransactionID doesn't have leading zeros so I need help to add the leading zeros. I'm using SQL Server Management Studio.

CREATE TABLE TMP_TOC_FINAL (
      Agency_Code VARCHAR(3)
    , Consumer_ID INT
    , Program_Area_ID INT
    , Discharge_Status VARCHAR(2)
    , Admit_Date NVARCHAR(20)
    , Discharge_Date NVARCHAR(20)
    , TransactionID INT identity (1,1) Primary key

The real programmer left and I'm trying my best to fix where he left off.

Thanks!

I changed the tag to SQL Server. This is clearly not MySQL code. — Gordon Linoff, Feb 05 '18 at 20:11
I suggest you read this thread https://dba.stackexchange.com/questions/15052/how-do-i-preserve-the-leading-zeros-when-i-insert-a-number-into-this-table — , Feb 05 '18 at 20:12
https://stackoverflow.com/questions/3429284/sql-identity-with-leading-padded-zeros — Tab Alleman, Feb 05 '18 at 21:37

score 3 · Answer 1 · answered Feb 05 '18 at 20:13

3

If you are using SQL Server, you are in luck. You can use computed columns. I would put the identity column first, so the table would look like this:

CREATE TABLE TMP_TOC_FINAL (
     TransactionID INT identity (1,1) Primary key,
     Agency_Code VARCHAR(3),
     Consumer_ID INT,
     Program_Area_ID INT,
     Discharge_Status VARCHAR(,2),
     Admit_Date NVARCHAR(20)
     Discharge_Date NVARCHAR(20), 
     TransactionId_str as (right(replicate('0', 12) + cast(TransactionID as varchar(12)), 12)
);

You can now use TransactionId_str for your output. I should note that integers max out around 2,000,000,000 -- that is 10 digits, not 12.

answered Feb 05 '18 at 20:13

Gordon Linoff

1,242,037
58
646
786

can you please explain this line TransactionId_str as (right(replicate('0', 12) + cast(TransactionID as varchar(12)), 12) ? – Suvam Roy Feb 05 '18 at 20:19
Thanks the last line worked. My only problem now is that the file should only have 7 columns and with the TransactionID INT identity (1,1) Primary key on the top - it counts as a column. Is there a way to hide this? – E Flojo Feb 05 '18 at 20:45
@EFlojo . . . Not really. You might need to use a view if you don't want the base table to seem like it has an additional column. However, I would suggest that you list out the columns you want rather than using `*`. That might solve your problem. – Gordon Linoff Feb 06 '18 at 04:18
Thank you Gordon! – E Flojo Feb 06 '18 at 14:02

How to add leading zeros to a auto numbering ID

1 Answers1