Reply to topic

NOT LIKE

wmae
Registered Member
Posts
32
Karma
0

NOT LIKE

Tue Feb 25, 2014 2:52 am
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
User avatar jstaniek
Moderator
Posts
926
Karma
2
OS

Re: NOT LIKE

Thu Mar 13, 2014 10:13 am
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 :)


Best regards,
Jarosław Staniek
Image

KEXI - Open Source Visual DB Apps Builder
• Request feature or fix for KEXI here
May I help you? Please mention your app's version and OS when asking for help
User avatar jstaniek
Moderator
Posts
926
Karma
2
OS

Re: NOT LIKE

Thu Mar 13, 2014 10:21 am
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.


Best regards,
Jarosław Staniek
Image

KEXI - Open Source Visual DB Apps Builder
• Request feature or fix for KEXI here
May I help you? Please mention your app's version and OS when asking for help
wmae
Registered Member
Posts
32
Karma
0

Re: NOT LIKE

Thu Mar 13, 2014 7:21 pm
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
User avatar jstaniek
Moderator
Posts
926
Karma
2
OS

Re: NOT LIKE  Topic is solved

Fri Mar 14, 2014 7:27 am
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%')


Best regards,
Jarosław Staniek
Image

KEXI - Open Source Visual DB Apps Builder
• Request feature or fix for KEXI here
May I help you? Please mention your app's version and OS when asking for help
wmae
Registered Member
Posts
32
Karma
0

Re: NOT LIKE

Fri Mar 14, 2014 5:50 pm
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
User avatar jstaniek
Moderator
Posts
926
Karma
2
OS

Re: NOT LIKE

Fri Mar 14, 2014 9:56 pm
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


Best regards,
Jarosław Staniek
Image

KEXI - Open Source Visual DB Apps Builder
• Request feature or fix for KEXI here
May I help you? Please mention your app's version and OS when asking for help

 
Reply to topic

Bookmarks



Who is online

Registered users: Baidu [Spider], Bing [Bot], claydoh, dbergstein, ghevan, Google [Bot], Jay 5gzmn, Majestic-12 [Bot], Snudl, Sogou [Bot], Sugarmaster, TheraHedwig, Tooniis