7

I am trying to add a auto_increment primary key with ZEROFILL with a max size of six.

So it outputs:

000001
000002 etc...

However I am really struggling to achieve this and cant find the answer. How do I set up this column type in SQL Server Management Studio?

Thanks

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Austin
  • 197
  • 1
  • 1
  • 4
  • 3
    Why are you trying to do this? Is it for display? If so, best to do on that end instead of db. Not sure you can do this in MSSQL. – sgeddes Feb 27 '13 at 13:38
  • You mentioned SSMS, so you are using MS SQL Server, wich version? add the needed tag – Yaroslav Feb 27 '13 at 13:39
  • Sorry the field type is a int not a Varchar as first mentioned. I have added the tags. – Austin Feb 27 '13 at 13:42

4 Answers4

10

You cannot do this with an integer field in SQL Server (nor would I recommend it with a Varchar).

Let SQL Server store the field as an Identity, and then (assuming this is for display), format the data when you select it like such:

SELECT RIGHT('000000' + CONVERT(VARCHAR(6),ID), 6) FROM Table
sgeddes
  • 62,311
  • 6
  • 61
  • 83
  • The field is not for display its for interfacing with other software. – Austin Feb 27 '13 at 13:48
  • 1
    @Austin -- perhaps use the identity field as such and then create a view with the above. Depends on your needs. This is just one way to achieve leading zeroes. – sgeddes Feb 27 '13 at 13:52
2

What you're trying to achieve is not possible. Display formatting is done in a presentation layer and not in the database. You need to separate a value from the presentation of a value. The two values 1 and 000001 are the same.

If you want to return something formatted, then you would have to return the value as a string. Just cast it to a string, add a number of zeroes at beginning and then keep the leftmost n characters.

Yaroslav
  • 6,476
  • 10
  • 48
  • 89
1
SELECT FORMAT(2, N'000000')
--OR
SELECT FORMAT(2, CAST(REPLICATE(0, 6) AS NVARCHAR(6)))

https://learn.microsoft.com/en-us/sql/t-sql/functions/format-transact-sql?view=sql-server-ver15

AbbasAli Hashemian
  • 168
  • 1
  • 7
0

By creating a function

CREATE FUNCTION MyZeroFill (
 @N BIGINT,
 @D SMALLINT
 ) RETURNS VARCHAR(50) AS
BEGIN
RETURN RIGHT('0000000000000000000000000000000000000000000000'+CAST
(@N AS VARCHAR),@D)
END
Bellash
  • 7,560
  • 6
  • 53
  • 86