SQLite, check if TEXT field has any alphabetical c

问题:

Okay, so I have a huge list of entries, and in one of the columns (for simplicity let's call it num there's a number, something like 123456780000 (they are all the same length and format), but sometimes there are fields that look something like this

12345678E000 
or 
12345678H000

Now, I need to delete all the rows in which the num column is not entirely numeric. The type of num is TEXT, not INTEGER. So the above examples should be deleted, while 123456780000 should not.

I have tried two solutions, of which one works but is inelegant and messy, and the other one doesn't work at all.

The first thing I tried is

DELETE FROM MY_TABLE WHERE abs(num) == 0.0

Because according to the documentation, abs(X) returns exactly 0.0 if a TEXT value is given and is unconvertable to an real number. So I was thinking it should let all the "numbers-only" pass and delete the ones with a character in it. But it doesn't do a thing, it doesn't delete even a single row.

The next thing I tried is

DELETE FROM MY_TABLE WHERE num LIKE "%A%" OR "%B%" OR "%C%"

Which seems to work, but the database is large and I am not sure which characters can appear, and while I could just do "%D%" OR "%E%" OR "%F%" OR ... with the entire alphabet, this feels inelegant and messy. And I actually want to learn something about the SQLite language.

My question, finally, is: how do I solve this problem in a nice and simple way? Perhaps there's something I'm doing wrong with the abs(X) solution, or is there another way that I do not know of/thought of?

Thanks in advance.

EDIT: According to a comment I tried SELECT abs(num) FROM MY_TABLE WHERE num like '%A%' and it returned the following

12345678.0

That's strange. It seems it has split the number where the alphabetical appeared. The documentation claimed it would return 0.0 if it couldn't convert it to a number. Hmm..

回答1:

You can use GLOB in SQLite with a range to single them out:

SELECT * 
FROM MY_TABLE
WHERE num GLOB '*[A-Za-z]*'

See it in use with this fiddle: http://sqlfiddle.com/#!7/4bc21/10

For example, for these records:

   num
----------
1234567890
0987654321
1000000000
1000A00000
1000B00000
1000c00000

GLOB '*[A-Za-z]*' will return these three:

   num
----------
1000A00000
1000B00000
1000c00000

You can then translate that to the appropriate DELETE:

DELETE 
FROM MY_TABLE
WHERE num GLOB '*[A-Za-z]*'