SQL - Numeric data type with leading zeros

I need to store Medicare APC codes. I believe the format requires 4 numbers. Leading zeros are relevant. Is there any way to store this data t开发者_高级运维ype with verification? How should I store this data (varchar(4), int)?

This kind of issue, storing zero leading numbers that need to be treated as Numeric values on some scenarios (i.e. sorting) and as textual values in others (i.e. addresses) is always a pain and there is no one answer that is best for all users. At my company we have a database that stores numbers as text for codes (not Medicare APC codes) and we must pad them with zero’s so they will sort properly when used in an order operation.

Do not use a numeric data type for this because the item is not a true number but textual data that uses numeric characters. You will not be performing any calculations or aggregates on the codes and so the only benefit to storing them as a number would be to ensure proper sorting of the codes and that can be done with the code stored as text by padding it with zeros where needed. If you sue a numeric data type then any time the code is combined with other textual values you will have to explicitly convert it to CHAR/VARCHAR or let SQL Server do it since implicit conversions should always be avoided that means a lot of extra work for you and the query processor any time the code is used.

Assuming you decide to go with a textual data type the question then is should you use VARCHAR or CHAR and while many who have posted say VARCHAR I would suggest you go with CHAR set to a length of 4. WHY?

The VARCHAR data type is for textual data in which the size (the length or number of characters) is unknown in advance. For this Medicare code we know the length will always be at least 4 and possibly no more than 4 for the foreseeable future. SQL Server handles the storage of the data differently between CHAR and VARCHAR. SQL Server’s BOL (Books On Line) says :

Use CHAR when the size of the column data entries are consistent

Use VARCHAR when the size of the column data varies considerably.

I can’t say for certain this is true for SQL Server 2008 and up but for earlier versions, the use of a VARCHAR data type carries an extra overhead of 1 byte per row of data per column in a table that has a VARCHAR data type. If the data stored is always the same size and in your scenario it sounds like it is then this extra byte is a waste.

In the end it’s up to you as to whether you like CHAR or VARCHAR better but definitely don’t use a numeric data type to store a fixed length code.

That's not numeric data; it's textual data that happens to contain digits.

Use a VARCHAR.

I agree, using

CHAR(4)

for the check constraint use

check( APC_ODE LIKE '[0-9][0-9][0-9][0-9]' )

This will force a 4 digit number only to be accepted...

varchar(4)

optionally, you can still add a check constraint to ensure the data is numeric with leading zeros. This example will throw exceptions in Oracle. In other RDBMS, you could use regular expression checks:

alter table X add constraint C 
check (cast(APC_CODE as int) = cast(APC_CODE as int))

If you are certain that the APC codes will always be numeric (that is if it wouldn't change in the near future), a better way would be to leave the database column as is, and handle the formatting (to include leading zeros) at places where you use this field values.

If you need leading 0s, then you must use a varchar or other string data type.

There are ways to format the output for leading 0s without compromising your actual data.

See this blog entry for an easy method.

CHAR(4) seems more appropriate to me (if I understood you right, and the code is always 4 digits).

What you want to use is a VARCHAR data type with a CHECK constraint, using LIKE with a pattern to check for numeric values.

in TSQL

check( isnumeric(APC_ODE) = 1)