Use CONVERT/CAST to change #N/A to NULL

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Use CONVERT/CAST to change #N/A to NULL

Author

Topic

daman
Yak Posting Veteran

72 Posts

Posted - 2007-11-23 : 15:46:21

My input would have #N/A or #NUM, #VALUE because I read them from CSV file.

I would read that input into a table with float type.

So if I read in
2.3
#N/A
3.2

I would like to get it stored in my table as

2.3
NULL
3.2

or whatever format is preferred so an error is represented.

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-11-23 : 16:04:58

When you are reading it in transform using IsNumeric() = 1

If IsNumeric([Field]) =1 then [Field]

if it isn't numeric it will NULL

Poor planning on your part does not constitute an emergency on my part.