UPC Codes

How to efficiently store in MS SQL 2005?

[Easy_Coder]

If you're using upc codes what data type did you select?

I need to store a ucc 12 code which is a 12 digit number and leave open the possibility that the system will need to expand to 14-digit GTIN.

The numeric types cant handle leading zeros which upc codes might have so am I stuck with varchar?

[txbakers]

I would use varchar for barcodes.

BUT, if you wanted to use INT, you could always first get the length of the String version, and if 11, add a leading 0.

[aspdaddy]

You could also store it as a bigint and use a computed column, formulae, or view to add the leading zero's.

[Demaestro]

I would stay away from the any int field and just keep it a varchar(32) or something like that. I am not knowlagable about UPC codes but is it not conceivable that they may one day contain non-numeric values?

[Easy_Coder]

thanks for the ideas... I'm going with varchar(14)

[aspdaddy]

Thats sounds the right way to go.

you could always first get the length of the String version, and if 11, add a leading 0.

In SQL Server you can use replicate to do this automatically using the forumale field in enterprise manager

(right((replicate('0',6) + convert(varchar(6),[ID])),6))