How to set Sqlite3 to be case insensitive when str

2019-01-02 16:59发布

I want to select records from sqlite3 database by string matching. But if I use '=' in the where clause, I found that sqlite3 is case sensitive. Can anyone tell me how to use string comparing case-insensitive?

10条回答
其实,你不懂
2楼-- · 2019-01-02 17:09

If the column is of type char then you need to append the value you are querying with spaces, please refer to this question here . This in addition to using COLLATE NOCASE or one of the other solutions (upper(), etc).

查看更多
裙下三千臣
3楼-- · 2019-01-02 17:11

This is not specific to sqlite but you can just do

SELECT * FROM ... WHERE UPPER(name) = UPPER('someone')
查看更多
荒废的爱情
4楼-- · 2019-01-02 17:11

Another option that may or may not make sense in your case, is to actually have a separate column with pre-lowerscored values of your existing column. This can be populated using the SQLite function LOWER(), and you can then perform matching on this column instead.

Obviously, it adds redundancy and a potential for inconsistency, but if your data is static it might be a suitable option.

查看更多
忆尘夕之涩
5楼-- · 2019-01-02 17:13

Simply, you can use COLLATE NOCASE in your SELECT query:

SELECT * FROM ... WHERE name = 'someone' COLLATE NOCASE
查看更多
永恒的永恒
6楼-- · 2019-01-02 17:26

You can do it like this:

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

(It's not the solution, but in some cases is very convenient)

"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.)."

查看更多
还给你的自由
7楼-- · 2019-01-02 17:27
SELECT * FROM ... WHERE name = 'someone' COLLATE NOCASE
查看更多
登录 后发表回答