Member Avatar for 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.

  • 2 Contributors
  • 2 Replies
  • 160 Views
  • 3 Hours Discussion Span
  • Latest Post Latest Post by Stuugie
Member Avatar for Ancient Dragon

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

Member Avatar for 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.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.