Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 341
  • Last Modified:

How to handle N/A or Unknown Data

Hi,

I have a field that takes numbers, a double type 100.45. However, the user says that sometimes the value is unknown. So, there are like two states for this field:

1. A number 0 to 1,000
2. Unknown or N/A value. Somebody has not measured it yet, so there is no value yet.

How can I design my table or the column to handle values like this and cover both states? I like to have a column with numbers only, and no chars in it to keep data integrity.

Any suggestions on how to handle this?

Thanks


0
gtrapp
Asked:
gtrapp
4 Solutions
 
Paul JacksonSoftware EngineerCommented:
Just allow nulls in the column definition.
0
 
SharathData EngineerCommented:
If possible, define your column as decimal(6,2) and insert N/A, Unknown values as NULL. If that is not possible and you have to have N/A and Unknown,
you have to define your column as varchar to hold string data.
0
 
Ephraim WangoyaCommented:

The best way is to use nulls for unassigned values
0
A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

 
Paul JacksonSoftware EngineerCommented:
If you allow nulls when you do a select you can handle the null using the ISNULL function like this :

Select ISNULL(mycolumn, 'N/A')
FROM mytable

This will return the column value if not null and return what is specified in the quotes if it is null.
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
>> 1. A number 0 to 1,000
2. Unknown or N/A value.

Since you have stored character values in a numeric column, the best way without altering the table would be to use ISNUMERIC function as given below:

SELECT * FROM ur_table
WHERE ISNUMERIC(ur_column_name) = 1

If you are ok to change table structure along with data, then change your column datatype to INT and change those invalid characters as 0 or null..
0
 
gtrappAuthor Commented:
Thanks for the advice,
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now