I'm using SQLlite.
I have a table like so
| Name | Data
|--------------------|--------------
| Nestle Milo 1kg | ABC
| Milo Chocolate 1kg | DFE
I want to select all rows that sort of "roughly match" each other on this name column. In this instance, they both feature "Milo" and "1kg". Maybe the selection result gives a "match score" or something like that?
My real-world use case is I've scraped two major grocery stores online catalogues for prices. I want to now compare the prices of items across the two stores. However, they don't share SKUs/identifiers, and even the scraped names don't coincide, though obviously the names of products between the two stores have a LOT in common.
If you want a simpler solution and can tolerate less precision, you can use the LIKE operator with wildcards. This approach is less flexible but can be useful for simple matching.
-- Example query using LIKE operator
SELECT
Name,
Data
FROM
your_table
WHERE
Name LIKE '%Milo%' AND Name LIKE '%1kg%';
SQLite supports Full-Text Search (FTS), which can be used to search for terms within text. It won't give you a similarity score but can be useful for finding rows that contain similar terms.
-- Example query using FTS
SELECT
Name,
Data
FROM
your_table_fts
WHERE
Name MATCH 'Milo AND 1kg';