• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 499
  • Last Modified:

Test for NaN in a query

How can I use the where clause of a select query to identify records where a numeric field is NaN?

I tried:

SELECT * FROM myTable WHERE myNumericField = 'nan';

myField is defined as Float (or in other cases could be Double)

...and that didn't generate any errors, but also didn't return any results.  If I leave off the WHERE clause completely, I can easily see the text 'nan' in myNumericField, yet when I use the WHERE clause it claims that zero records are found which meet that criteria.

If I leave off the quotes (...WHERE myNumericField = nan) or use the IS operator (...WHERE myNumericField IS Nan), I get errors.

Thanks for any assistance you can provide,

Alan

0
smiley_strat
Asked:
smiley_strat
  • 2
1 Solution
 
JesterTooCommented:
Since columns are "typed" (meaining they can only contain data of the declared datatype or null) then any column which doesn't contain data matching its declared type must, therefore, be NULL... try this query:

   SELECT * FROM myTable WHERE myNumericField is null;

HTH,
Lynn
0
 
ellandrdCommented:
theres no point posting the same answer twice in this thread

Ellandrd
0
 
JesterTooCommented:
Ellandrd... to whom are you addressing this comment? And to what are you referring?
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

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