0

So I'm trying to exclude a bunch of different entries with specific words from my query and I was wondering if there was a more efficient/faster way of doing this over the method that I am currently using:

AND LOWER("Column") NOT LIKE LOWER('%word1%')
AND LOWER("Column") NOT LIKE LOWER('%word2%')
AND LOWER("Column") NOT LIKE LOWER('%word3%')
AND LOWER("Column") NOT LIKE LOWER('%word4%')
AND etc....

This works but it gets overly long and complex very quickly. I've tried a few other methods that I found from searching but none of them fit my use case.

I need the wild cards included because the rest of the entry changes between each record and the LOWER(...) is needed to make the query case in-sensitive as some entries are capitalised and some aren't.

Let me know if any more info is needed or you have suggestions about anything else I can change to make it better in general.

Here is the full SQL statement if the context helps:

SELECT "Description", "Debit Amount", "Balance", "Categories"
FROM "Transactions"
WHERE "Debit Amount" IS NOT NULL
AND LOWER("Description") NOT LIKE LOWER('%word1%')
AND LOWER("Description") NOT LIKE LOWER('%word2%')
AND LOWER("Description") NOT LIKE LOWER('%word3%')
AND LOWER("Description") NOT LIKE LOWER('%word4%')
AND etc....

ORDER BY 2 DESC
Jade8703
  • 1
  • 2
  • 2
    Unless there's an actual WHERE key = something, you're scanning the entire table. You're better off just reading the entire table with no WHERE clause and using code to select the rows you want. – Gilbert Le Blanc May 13 '22 at 03:47
  • Let me add the entire SQL statement to my question so I can put it in context. Should have done this in the first place sorry – Jade8703 May 13 '22 at 04:37
  • 2
    Which DB type do you use? – Jonas Metzler May 13 '22 at 04:57
  • Currently it's just a personal one that I have as a file on my computer. It isn't on a server anywhere or anything like that. Currently I'm just playing around with it and SQL in general and trying to see if there's any good reason to make it anything more than something on my computer. – Jade8703 May 13 '22 at 05:15
  • What database product are you using? SqlServer? Oracle? PostGreSql? Teradata? etc.. – tinazmu May 13 '22 at 05:16
  • Is your database really case sensitive (why do you need LOWER)? Also LOWER('%word1%') is unnecessary because the string is already lowercase. – tinazmu May 13 '22 at 05:17
  • Please, add a tag of your DBMS. [Why should I tag my RDBMS](https://meta.stackoverflow.com/questions/388759/why-should-i-tag-my-rdbms) – astentx May 13 '22 at 05:27
  • Sorry I didn't know what people were asking about until astenix linked that other thread; currently I'm just using the software that was already installed on my system which is LibreOffice Base. I just wanted to have a play around with some data and wasn't bothered enough yet to download a new DBMS. If people recommend I do and give me a few suggestions, I will look into it. – Jade8703 May 13 '22 at 06:04
  • To answer your question tinazmu, yes, the DB is case sensitive. Some entries have only the first letter capitalized, others have the whole string capitalized but they are all entries that I want to group together. – Jade8703 May 13 '22 at 06:09
  • I'm sorry, it wasn't very clear what you were referring to and I haven't had much to do with DB's. Where could I find this info in Base so I could make sure you get the answer you want? – Jade8703 May 13 '22 at 06:15
  • I don't know about LibreOffice Base, but in any case, the query will have to go through every record for this. To make it faster, you could have a column that contains the lower case version of "Description". At least then the query would not have to do the conversion to lowercase on the column every time you run this. Keep in mind that the right side of all your "AND", are *already* in lower case, so you don't have to make the query attempt to make a lower case conversion for these. You may want to see if LibreOffice Base will take a regex instead of a long list of "AND" clauses. – Scratte May 13 '22 at 11:52
  • LibreOffice Base isn't the database engine, but rather an interface to it. Perhaps you are using HSQLDB 1.8, Firebird Embedded, or a split database setup such as LO Base connected to a separate PostgreSQL installation. You would have made this choice when creating the file. – Jim K May 14 '22 at 14:00
  • @JimK Ok so after much looking around and no guidance, I figured out it has to be using HSQLDB. It's also saying embedded if that makes any difference. – Jade8703 May 15 '22 at 23:35
  • Here is the documentation for the version of HSQLDB that you probably have: http://www.hsqldb.org/doc/1.8/guide/guide.html. As you may notice, it is very old, and yes, the case sensitivity rules can be tricky. It's better to use a split database setup instead such as by installing PostgreSQL and then connecting LibreOffice Base to it. This doesn't address the problem you asked about though. What do you think of @GilbertLeBlanc's suggestion? For example, you could write a LibreOffice python macro. Base macros are not easy to learn, so you'd probably need to ask for help if you try it. – Jim K May 16 '22 at 13:47

0 Answers0