8/16/2023 0 Comments Sqlite database browser filter![]() ![]() If you need to do this regularly you might create an an index as follows to address this: create index ctl_idx on ctl (substr(partner_sku, 1, instr(partner_sku, '-')-1)) Īfter which: select * from ctl where substr(partner_sku, 1, instr(partner_sku, '-')-1) in ('A1012', 'A207', 'KH108') This will, of course, require a full scan so it will work just fine if the table ctl has 10 rows (such as when testing) but will not be performant if the table ctl has a billion rows - depending on if other conditions in the where clause can cull the candidates which need testing to a relatively small number before this condition is tested. I am trying here to pass the query with few letters of some items to get desired set of product_sku. The first 3-4 letters of partner_sku are similar for a bunch of items because they are variants of same item which is the basis of my filtration say 'A1012'. ![]() I would definitely dive deep into JOIN commands as well as DATABASE NORMALIZATION to explore more about handling data efficiently.Īs you pointed out the need of another table having set of criteria and then running a query with JOIN command, the partner_sku is basically a uniquie product id in this catalogue table and other columns are having some related information like price, barcode etc. Thank you so much LARRY BRASTFIELD for your quick reply, understanding of the matter and kind suggestions. If you truly need LIKE expressions, (after such normalization as you see fit to perform), they are not going to go into an IN clause in a simple way. I read about WHERE with IN CLAUSE but don't know how to incorporate that here. Even a short tutorial on it could benefit your schema design. It's a big subject, but important for serious database work. If this makes no sense yet, you probably should study "database normalization". That refinement might reduce the need for extensive WHERE clauses. Seeing its values' leading letters makes me suspect they encode information that should occupy more than one column in a table. I also wonder if your "partner_sku" is not a composition. But if those LIKE terms represent a set derived logically from other data, there should probably be another table (or more) so that you can express your selection as a join. ![]() If that WHERE clause represents just a happenstance set of criteria, then a somewhat repetitive condition is harmless. SELECT partner_sku FROM ctl WHERE partner_sku LIKE 'A1012%' OR partner_sku LIKE 'A207%' OR partner_sku LIKE 'KH108%' įor few number of criteria in this query is alright, but as number grows it looks mess to handle. I almost cut that table source as not germane. I have imported a csv file called 'CATALOGUE.csv' to a table called 'ctl' and running a query to filter the column 'partner_sku' for strings of certain pattern as below I will presume you're new to SQL as well. I am very new to SQLite and still learning. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |