Search code examples
sqlsqlitepattern-matchingsql-like

SQLite Pattern Matching (APPEARS not CONTAINS)

Normally, pattern matching is done on a “search DB column for query text string” basis - i.e...

DB string “A BMW is a fancy car”
Query string “BMW”
SQL: “SELECT car FROM cars WHERE car LIKE ‘%BMW%’;

...assuming that I want to find a matching string in a DB.

However, now I want to do it the other way round:

DB string “BMW”
Query string “A BMW is a fancy car”

Instead of saying “does the string in the db CONTAIN my query string” I want to say “does the string in the db APPEAR in my query string”.

Solution

  • You can reverse the condition by concatenating % to the db column and then use it for matching the query string like this:

    SELECT car FROM cars WHERE 'A BMW is a fancy car' like '%' || car || '%';