我想通过字符串匹配从sqlite3数据库选择记录。但是如果我在where子句中使用'=',我发现sqlite3是区分大小写的。有人能告诉我如何使用字符串比较不区分大小写吗?


当前回答

SELECT * FROM ... WHERE name = 'someone' COLLATE NOCASE

其他回答

SELECT * FROM ... WHERE name = 'someone' COLLATE NOCASE

你可以这样做:

SELECT * FROM ... WHERE name LIKE 'someone'

(这不是解决方案,但在某些情况下非常方便)

"The LIKE operator does a pattern matching comparison. The operand to the right contains the pattern, the left hand operand contains the string to match against the pattern. A percent symbol ("%") in the pattern matches any sequence of zero or more characters in the string. An underscore ("_") in the pattern matches any single character in the string. Any other character matches itself or its lower/upper case equivalent (i.e. case-insensitive matching). (A bug: SQLite only understands upper/lower case for ASCII characters. The LIKE operator is case sensitive for unicode characters that are beyond the ASCII range. For example, the expression 'a' LIKE 'A' is TRUE but 'æ' LIKE 'Æ' is FALSE.)."

另一种选择是创建自己的自定义排序规则。然后可以在列上设置排序规则,或将其添加到选择子句中。它将用于排序和比较。

这可以用来使'VOILA'像'voilà'。

http://www.sqlite.org/capi3ref.html#sqlite3_create_collation

排序函数必须返回一个整数,如果第一个字符串分别小于、等于或大于第二个字符串,则该整数为负、零或正。

如果列的类型是char,那么你需要用空格来附加你正在查询的值,请参考这里的问题。除此之外,还可以使用COLLATE NOCASE或其他解决方案(upper()等)。

这对我很有效。 从表_name中选择测试名