1

I have a field containing some values. In edit mode, when I click on any one of these values it gets replaced by 1.#QNAN in the cell.

I would like to query the table for all cells containing 1.#QNAN values. When I try to query for Null values (SELECT FROM .... WHERE .... IS NULL) no rows get selected. So Im assuming SQL doesn't recognize that 1.#QNAN are in fact null values.

How do I get the select by attributes dialog box to select all cells not containing a number?

3
  • 1
    NaN (Not a number) is a complex type and is handled differently between databases, please indicate the exact storage for these features. You might not be able to select 1.#QNAN due to database/SQL limitations - perhaps instead select all the 'good' values and then invert selection. Aug 21, 2015 at 5:59
  • Im not sure what is they exact storage. They are of data type double and generated from a relate with a publicly available shapefile. Please indicate to me how I can use SQL to select values which ARE a number. I would consider this a valid answer.
    – user32882
    Aug 21, 2015 at 6:03
  • 1
    Try IS NOT NULL to select any valid value (I know NaN is neither null nor not null) then switch selection... to select unmatched records in a join I select primary_key = related.primary_key then switch, that might work in this case. Aug 26, 2015 at 22:27

2 Answers 2

2

As commented by @MichaelMiles-Stimson:

NaN (Not a number) is a complex type and is handled differently between databases, please indicate the exact storage for these features. You might not be able to select 1.#QNAN due to database/SQL limitations - perhaps instead select all the 'good' values and then invert selection.

and

Try IS NOT NULL to select any valid value (I know NaN is neither null nor not null) then switch selection... to select unmatched records in a join I select primary_key = related.primary_key then switch, that might work in this case.

0

You can use:

SELECT *
FROM TABLE
WHERE str(COLUMN) = ' nan'

As '1.#QNAN' should appear as ' nan' when converted to a string.

2
  • Asterisk is not a valid wildcard in SQL.
    – Vince
    May 31, 2022 at 4:15
  • My mistake, I was using this SQL query in MS Access where asterisks are valid. I've updated the answer so it works for more general cases.
    – DotPi
    May 31, 2022 at 4:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.