Test for NaN in a query

Posted on 2005-05-13
Last Modified: 2010-05-19
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,


Question by:smiley_strat
    LVL 20

    Accepted Solution

    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;

    LVL 16

    Expert Comment

    theres no point posting the same answer twice in this thread

    LVL 20

    Expert Comment

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

    Featured Post

    Live: Real-Time Solutions, Start Here

    Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

    Join & Write a Comment

    A lot of articles have been written on splitting mysqldump and grabbing the required tables. A long while back, when Shlomi ( had suggested a “sed” way, I actually shell …
    As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
    To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…
    Here's a very brief overview of the methods PRTG Network Monitor ( offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

    733 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now