SQLite，检查 TEXT 字段中是否有任何字母字符 [英] SQLite, check if TEXT field has any alphabetical chars in it

问题描述

好的，所以我有一个巨大的条目列表，并且在其中一列中(为简单起见，我们称之为 num 有一个数字，类似于 123456780000(它们的长度和格式都是一样的)，但有时有些字段看起来像这样

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

现在，我需要删除 num 列不完全是数字的所有行.num 的类型是 TEXT，而不是 INTEGER.所以上面的例子应该删除，而123456780000不应该.

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.

我尝试的第一件事是

DELETE FROM MY_TABLE WHERE abs(num) == 0.0

因为根据文档，如果给定 TEXT 值并且无法转换为实数，则 abs(X) 将准确返回 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.

接下来我尝试的是

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

这似乎有效，但数据库很大，我不确定可以出现哪些字符，虽然我可以做 "%D%" OR "%E%" OR "%F%" OR... 整个字母表，这感觉不雅和凌乱.而且我实际上想了解一些有关 SQLite 语言的知识.

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.

最后，我的问题是:我如何以一种既简单又好用的方式解决这个问题?也许我在 abs(X) 解决方案上做错了什么，或者还有其他我不知道/不知道的方法吗?

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?

提前致谢.

根据评论，我尝试了 SELECT abs(num) FROM MY_TABLE WHERE num like '%A%'它返回了以下内容

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

12345678.0

这很奇怪.似乎它已经分割了字母出现的数字.该文档声称如果无法将其转换为数字，它将返回 0.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..

推荐答案

您可以使用 GLOB 在 SQLite 中，有一个范围可以将它们挑出来:

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

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

看到它与这个小提琴一起使用:http://sqlfiddle.com/#!7/4bc21/10

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

例如，对于这些记录:

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

GLOB '*[A-Za-z]*' 将返回这三个:

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

然后您可以将其转换为适当的DELETE:

You can then translate that to the appropriate DELETE:

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

这篇关于SQLite，检查 TEXT 字段中是否有任何字母字符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！