How to search for values of several columns at once that start with some string-CodePudding

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 isbn, name and 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?

CodePudding user response：

Use LIKE:

WHERE LOWER(isbn || ' ' || name || ' ' || author) LIKE '%proust%'

Unlike the proprietary functions you used, this code will run on all RDBMS.