Best Practices Regarding Data Types and Values

[Stuugie]

Hi All,

I sort of want to get a "best practices" perspective regarding numeric values. First, I'll give my scenario:
In my SSIS package, a CSV file is used for data extraction. Once extracted, a staging table is loaded with all the values from the CSV. One column, called "IPI_Value" is a numeric (18,2) type. However, some of the data being loaded contain the value 'x' if there is no numeric value in the dataset. Obviously I can't store 'x' in a numeric field within the database so I came up with:

update a
set a.IPI_Value = REPLACE(a.IPI_Value,'x','0')
from dbo.tblIPIStaging a
where a.IPI_Value='x'

The question I have is whether or not the value being replacing the 'x' should be a '0' or NULL. A 0, in my opinion can be misleading when analysing the data afterwards as the 'x' represents a non-value for a given timeframe. So what is the best practice then, replacing with 0 or a NULL? Or does it not really matter.

I know at query time the query can rule out either values with 0s or NULLs so....

Anyways, the people of this forum always give me the best advice so I anticipate your suggestions.

[Ancient Dragon]

Replace with NULL to indicate it has no value, assuming the field is allowed to have NULL values.

[Stuugie]

I did that and yes the field accepts nulls. I think 0s will mess up analysis so I made up my mind on this shortly after I posted.