SQLiteDatabase.SelectSQL result is case sensitive. How do I get case-insensitive results from a SelectSQL?

Databases
1

I create an in-memory database for a Mac/Win desktop app:

Dim db As new SQLiteDatabase

and add a table of data. When I call:

Dim rSet As RowSet = db.SelectSQL(“SELECT * FROM " + TableName + " WHERE " + FindPropName + " = ?”, FindPropValue)

rSet only contains records where all characters in FindPropValue are the same case (upper/lower) as what is stored in the database.

I read that databases by default are case-insensitive. Is this the case, and also with a SQLiteDatabase in Xojo? Or do I need to use a different SelectSQL command to get the result in a case-insensitive way?

Sorry haven’t worked much with databases. Many thanks in advance.

2

You can use collate nocase either in your query or when you create the table:

select * from table where field = 'BLAH' collate nocase

or

create table tablename(fieldname text collate nocase)

1 Like
3

You can also define the columns (when you create the database table) as ‘collate nocase’.

1 Like
4

You can alternatively use LIKE instead of =

By the way if you post code, please:

  1. Put the code in your post
  2. Select it all with the mouse
  3. Click on the </> button

TIA.

5

Thanks. Will do

1 Like