LIKE '%search_item%' query works as a case sensitive LIKE 'search_item%' in QMYSQL QSqlDatabase connection

arost

query.prepare("SELECT code FROM stuff WHERE code LIKE '%"+text+"%'");

//query.prepare("SELECT code FROM stuff WHERE code LIKE '%%"+text+"%%'");

//query.prepare("SELECT code FROM stuff WHERE code LIKE ?");
//query.addBindValue("%"+text+"%");

//query.prepare("SELECT code FROM stuff WHERE code LIKE :input");
//query.bindValue(":input", QString("%%1%").arg(text));

all the above code (first line or commented in the next lines) return the same results (as LIKE text%) without any error

SGaist

Hi and welcome to devnet,

Did you try printing the query generated to see what is actually sent to the database ?

arost

@SGaist

Yes, Thx
when the text is "5" and "55", the prepared queries are
"SELECT code FROM stuff WHERE code LIKE '%5%'"
"SELECT code FROM stuff WHERE code LIKE '%55%'"
respectively.

    QString qry = "SELECT code FROM stuff WHERE code LIKE '%"+text+"%'";
    query.prepare(qry);
    qDebug() << qry;

SGaist

I meant QSqlQuery::lastQuery.

arost

@SGaist said in LIKE '%search_item%' query works as a case sensitive LIKE 'search_item%' in QMYSQL QSqlDatabase connection:

QSqlQuery::lastQuery.

    QSqlQuery query(sdb->db);
    query.prepare("SELECT code FROM stuff WHERE code LIKE '%"+text+"%'");
    qDebug() << query.lastQuery();

12:31:04: Starting F:\data\store\build-store-Desktop_Qt_6_5_0_MinGW_64_bit-Debug\store.exe...
"SELECT code FROM stuff WHERE code LIKE '%5%'"
"SELECT code FROM stuff WHERE code LIKE '%55%'"
12:31:12: F:\data\store\build-store-Desktop_Qt_6_5_0_MinGW_64_bit-Debug\store.exe exited with code 0

Christian Ehrlicher

I doubt this has anything to do with the Qt mysql driver as the query is simply passed to the mysql lib. Therefore please provide a minimal, compilable example including the table creation & fill. One record should be enough.

arost

@Christian-Ehrlicher

it is an instance from a web hosted mysql mariadb database on my laptop as local host

Christian Ehrlicher

This does not matter - I can use a local mysql instance but need a minimal, reproducible example where you create a table, fill it and then do your problematic selects.

ChrisW67

@arost Case sensitivity in MariaDB/MySql depends on the character set and collation set on the column, table, database, server, and/or client-server connection. We cannot see any of these in your examples, but at least the last item in that list is the variable here. All the default character set collations are case insensitive but can be overridden. If a certain column or table should always compare with/without case-sensitivity then you should probably declare it that way at creation time.

If your code needs to be portable to another database system, e.g. Oracle or Sql Server, then it is safest to code generically using ANSI standard SQL functions:

select * from blah where lower(col) like '%foo%';

and make sure the user input "foo" is forced to lower case (in your code or in the SQL).

The examples, '%55%' and '%5%', are completely unaffected by case-sensitivity (There being no such beast as upper or lower-case numerals). If you intend to demonstrate case-sensitivity issues then don't use these in the minimal, compilable example including the table creation & fill requested by @Christian-Ehrlicher.

arost

Thanks All,

Unfortunately, the problem was default filter in the model and completer used to show the result in the app.

model = new QStringListModel(this);
completer = new QCompleter(this);
completer->setModel(model);
// This will ensure that the completer does not filter the data at all.
completer->setFilterMode(Qt::MatchFlags(Qt::MatchFlag::MatchContains));
completer->setCaseSensitivity(Qt::CaseInsensitive);

JonB

@arost
So it was always working correctly. This is why you should always be prepared to show a minimal example of your code.