I completely new to the world of databases and querying.
I have the following db
for which I want to construct a “smart” search query for to be able to fetch relevant entries based on a query that looks for something in the
author columns. The catch is that the input query doesn’t necessarily have to be a keyword, it can be part of one. I want to be able to enter a part of an isbn number, a part of a book’s name, a part of an author’s name and get back relevant results.
Say I query for “97182”, I want all the books whose isbn begins with that sequence.
Or if I query “ma”, I want all the entries whose name or author begins with “ma”.
For example I recently learned that one could do the following (I believe the name is “full text search”) using Postgresql:
SELECT * FROM books WHERE to_tsvector(isbn || ' ' || name || ' ' || author) @@ to_tsquery('proust')
Output will be the rows that have “proust” in one of the searched for columns.
The same postgresql query will not work for what I mentioned due to the nature of how it works (vectorizing columns, reducing to lexemes etc.). I have not fully understood full text search yet though.
Is it possible to create a “smarter” query according to my description?