SQL Server Management Studio ZEROFILL

Question

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

Matey

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. – Rexanna

You mentioned SSMS, so you are using MS SQL Server, wich version? add the needed tag – Leonardaleonardi

Sorry the field type is a int not a Varchar as first mentioned. I have added the tags. – Matey

Answer 1

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

Rexanna

The field is not for display its for interfacing with other software. – Matey

@Matey -- 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. – Rexanna

Answer 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.

Leonardaleonardi

Answer 3

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

Erhart

Answer 4

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

Saragossa