How to set Sqlite3 to be case insensitive when string comparing

Question

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

User · Answer

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

User · Answer

This is not specific to sqlite but you can just do  SELECT   FROM     WHERE UPPER name    UPPER  someone

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

Simply  you can use COLLATE NOCASE in your SELECT query   SELECT   FROM     WHERE name    someone  COLLATE NOCASE

User · Answer

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

User · Answer

Its working for me Perfectly   SELECT NAME FROM TABLE NAME WHERE NAME    test Name  COLLATE NOCASE

User · Answer

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

User · Answer

SELECT   FROM     WHERE name    someone  COLLATE NOCASE

[sqlite] How to set Sqlite3 to be case insensitive when string comparing?

Examples related to sqlite

Examples related to case-insensitive