ChatGPT解决这个技术问题 Extra ChatGPT

How to set Sqlite3 to be case insensitive when string comparing?

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?


s
sashoalm

You can use COLLATE NOCASE in your SELECT query:

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

Additionaly, in SQLite, you can indicate that a column should be case insensitive when you create the table by specifying collate nocase in the column definition (the other options are binary (the default) and rtrim; see here). You can specify collate nocase when you create an index as well. For example:

create table Test
(
  Text_Value  text collate nocase
);

insert into Test values ('A');
insert into Test values ('b');
insert into Test values ('C');

create index Test_Text_Value_Index
  on Test (Text_Value collate nocase);

Expressions involving Test.Text_Value should now be case insensitive. For example:

sqlite> select Text_Value from Test where Text_Value = 'B';
Text_Value      
----------------
b               

sqlite> select Text_Value from Test order by Text_Value;
Text_Value      
----------------
A               
b               
C    

sqlite> select Text_Value from Test order by Text_Value desc;
Text_Value      
----------------
C               
b               
A

The optimiser can also potentially make use of the index for case-insensitive searching and matching on the column. You can check this using the explain SQL command, e.g.:

sqlite> explain select Text_Value from Test where Text_Value = 'b';
addr              opcode          p1          p2          p3                               
----------------  --------------  ----------  ----------  ---------------------------------
0                 Goto            0           16                                           
1                 Integer         0           0                                            
2                 OpenRead        1           3           keyinfo(1,NOCASE)                
3                 SetNumColumns   1           2                                            
4                 String8         0           0           b                                
5                 IsNull          -1          14                                           
6                 MakeRecord      1           0           a                                
7                 MemStore        0           0                                            
8                 MoveGe          1           14                                           
9                 MemLoad         0           0                                            
10                IdxGE           1           14          +                                
11                Column          1           0                                            
12                Callback        1           0                                            
13                Next            1           9                                            
14                Close           1           0                                            
15                Halt            0           0                                            
16                Transaction     0           0                                            
17                VerifyCookie    0           4                                            
18                Goto            0           1                                            
19                Noop            0           0

After (re)creating the table with 'COLLATE NOCASE', I noticed it was much faster than the query WHERE name = 'someone' COLLATE NOCASE. MUCH faster (six to 10 times, roughly?)
According to the documentation, adding COLLATE NOCASE to the index is not required if the field itself already has this collation defined: "The default collating sequence is the collating sequence defined for that column in the CREATE TABLE statement."
COLLATE NOCASE will only work with ASCII text. Once you have "FIANCÉ" or "voilà" in your column values, it won't match against "fiancé" or "VOILA". After enabling the ICU extension, LIKE becomes case-insensitive, so 'FIANCÉ' LIKE 'fiancé' is true, but 'VOILA' LIKE 'voilà' is still false. And ICU+LIKE has the drawback on not using the index, so it can be slow on big tables.
select div,case when div = 'fail' then 'FAIL' else 'PASSED' end,* from marks collate nocase above didnt work am I doing some thing wrong?
One thing to note that tripped me up: select * from tbl where firstname='john' and lastname='doe' COLLATE NOCASE will be case insensitive on lastname. To be case insensitive on firstname, write this: select * from tbl where firstname='john' COLLATE NOCASE and lastname='doe'. It's specific to that one column, not the entire where clause.
C
Craz
SELECT * FROM ... WHERE name = 'someone' COLLATE NOCASE

If you are like me and want more documentation on Collating, you can find it here on this page :sqlite.org/datatype3.html Just scroll down to #6.0
@will: It's now point #7 and directly accessible with sqlite.org/datatype3.html#collating_sequences
E
EJoshuaS - Stand with Ukraine

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


@MM-BB yes, unless we perform the LIKE on a column which is declared (or indexed) as COLLATE NOCASE, it will do a full scan of the rows.
It is not a bug, it is a documented limitation. The same page quoted in the answer mentions the ICU extension that manages unicode characters. (Perhaps it wasn't the case in 2009)
This will produce unexpected results if your search contains %
@StevenPenny, yes, of course. The "LIKE" operator does a pattern matching comparison, which is mentioned in my answer.
o
oscarkuo

This is not specific to sqlite but you can just do

SELECT * FROM ... WHERE UPPER(name) = UPPER('someone')

The other part of the performance concern is finding the matching rows in the table. Does SQLite3 support function-based indexes? Indexing the search column or expression (e.g. "UPPER(name)") in a situation like this is usually a good idea.
Watch out with this one, as cheduardo hinted, SQLite cannot make use of an index on 'name' when running this query. The db engine will need to full-scan all rows, converting all of the 'name' fields to upper case and running the comparison.
This is ASCII only - something like Jóga will fail
N
Nick Ericson

Another option is to create your own custom collation. You can then set that collation on the column or add it to your select clauses. It will be used for ordering and comparisons.

This can be used to make 'VOILA' LIKE 'voilà'.

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

The collating function must return an integer that is negative, zero, or positive if the first string is less than, equal to, or greater than the second, respectively.


M
Magnus

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.


C
Community

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


S
Shohel Rana

Its working for me Perfectly. SELECT NAME FROM TABLE_NAME WHERE NAME = 'test Name' COLLATE NOCASE


P
Pullat Junaid

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

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

This was already answered long before you posted this.
R
Rasel Khan

use like this

 "select * from $pwsXDataHistory where type = '$type' COLLATE NOCASE and $t_uStatus != '$DELETE' order by $t_name COLLATE NOCASE asc ");

M
Mahendranatarajan

you can use the like query for comparing the respective string with table vales.

select column name from table_name where column name like 'respective comparing value';


This doesnt add anything to stackoverflow.com/a/973665/2462516 which was posted in 2009