How does one write the opposite of LIKE in a SELECT statement? I can include certain characters with the LIKE keyword but how do I do the opposite by excluding certain characters?

SELECT * FROM database WHERE subjecttitle LIKE '%xyz%' would fetch all instances where the characters "xyz" are contained in the subjecttitle. How do I fetch all instances where the characters "xyz" are not contained in the subjecttitle?
Kexi does not recognize NOT LIKE. Any leads would be greatly appreciated.

Thanks,
wmae
kexi 2.6.2

Hello wmae,
NOT LIKE is a missing feature. Would you be able to report a wish "Add support for NOT LIKE operator in queries" @ bugs.kde.org?
It would go to 2.8.1 if we do it quickly.

Thanks! And please suggest more great hints like these

Just noticed there's workaround thanks to the fact that LIKE is a boolean expression.

Instead of using

SELECT * FROM database WHERE subjecttitle NOT LIKE '%xyz%'

use

SELECT * FROM database WHERE NOT subjecttitle LIKE '%xyz%'

Still quite readable. Hope this helps.

This workaround seems to work for single items but I haven't been able to string several items together in the same SELECT statement:

SELECT * FROM database WHERE NOT subjecttitle LIKE '%xyz%' OR NOT subjecttitle LIKE '%abc%' OR NOT subjecttitle LIKE '%def%' OR NOT subjecttitle LIKE '%ghi%'

It doesn't seem to work. Been trying different variations of parentheses without success.

wmae

wmae wrote:This workaround seems to work for single items but I haven't been able to string several items together in the same SELECT statement:

SELECT * FROM database WHERE NOT subjecttitle LIKE '%xyz%' OR NOT subjecttitle LIKE '%abc%' OR NOT subjecttitle LIKE '%def%' OR NOT subjecttitle LIKE '%ghi%'

It doesn't seem to work. Been trying different variations of parentheses without success.

I think what you wanted in the above conditions is to reject xyz, abc, def and ghi. Note: 'and'. So if I understand your needs right, please just you replace ORs with ANDs. I tested this and works just fine as a fairy standard approach .

Using the original "NOT c1 OR NOT c2 OR NOT c3 OR NOT c4" is equivalent to "NOT (c1 AND c2 AND c3 AND c4)" i.e. finding texts that do not contain all the substrings 'xyz', 'abc', 'def', 'ghi' at once what's probably not what you want.

Hope this helps

PS: Boring formal background: http://en.wikipedia.org/wiki/De_Morgan%27s_laws
This also means that even shorter approach would be using NOT (c1 OR c2 OR ... ), i.e.:

SELECT * FROM database WHERE NOT (subjecttitle LIKE '%xyz%' OR subjecttitle LIKE '%abc%' OR subjecttitle LIKE '%def%' OR subjecttitle LIKE '%ghi%')

Thanks for the tip. I replaced OR with AND, made a few adjustments, and it worked. At first I thought AND would be too broad but the NOT influenced the AND to work like an OR.
On another note, I haven't been able to figure out how to submit a wish. Do I submit it as a bug? Also, does it require a different password than this forum's. Isn't it all part of KDE?

wmae

wmae wrote:On another note, I haven't been able to figure out how to submit a wish. Do I submit it as a bug?

Yes, on the very bottom of https://bugs.kde.org/enter_bug.cgi?prod ... mat=guided you have a Severity field. Select Wishlist there.

Registered contributors (I guess those with identity.kde.org account, no need to be KDE software developer) can have developer permissions to the bugs.kde.org assigned. They can change value of many fields, and also set Severity to Task what may be useful distinction.

wmae wrote:Also, does it require a different password than this forum's. Isn't it all part of KDE?

It's separate from identity.kde.org password. That's a rare exception.

I also recommend reading http://techbase.kde.org/Contribute/Bugs ... o_Bugzilla