What is the data type for barcodes?

Question

I have an Excel CSV file with a Barcode column that has data that looks like this: 5.06E+12 - it has a decimal number(5.06), letter(E) and symbol(+).

When I try to edit this in Excel, the number changes to 5060190000000.

When storing this type of data to my SQL Server database, what should the data type be of my Model's Barcode property?

Solution 1

Try to pick the most appropriate data type. If you're using actual product barcodes, a little research indicates that they're likely International Article Numbers.

Since they're really strings of 13 digits, a char(13) would probably be the most appropriate data type to store this data. Don't just default to varchar(50) because that's "big enough" - think of the length specification as free validation.

Solution 2

This is called E notation which is a variation of scientific notation. The number in question is an integer, but is abbreviated.

5.06 * 10^12 = 5060190000000

Thus, your value should be stored as an integer large enough to store your number.

Your value should be stored as a varchar long enough to fit the length of potential values.

Comments

[Gordon Linoff]

You should store this as a string . . . say, varchar(10). Normally, though, a value represented that way is storing a floating point number, which is why you see the full value in Excel.

[Robin Mackenzie]

support.office.com/en-us/article/…

[Bob Jarvis - Слава Україні]

@aladdin786 - VARCHAR would be an appropriate data type. The length of the field would depend on the amount of data actually encoded in the bar code.

[iamdave]

Depending on the range, potentially a bigint.

[Damien_The_Unbeliever]

The maximum number of digits for an int is 10 and even then that range is limited.

[Jørgen R]

I edited my answer to reflect that you need an integer large enough to store your number, such as a bigint if the number is sufficiently large.

[Damien_The_Unbeliever]

Also, if it's a barcode, it's likely that this should be dealt with as a string of digits. You're not going to add barcodes together or do other maths with them, and leading zeroes may be possible.

[Jørgen R]

@Damien_The_Unbeliever You are right. Edited my answer.