Building advanced SQL search from a user text input

We are going to build a program that parses user search query and serializes it into a SQL statement. Here is an example of a search query:

firstName:"Thomas" -jefferson birthdate:[1960 TO 1970] (profession:"inventor" OR profession:"engineer")

What this says is:

• Find records with first name “Thomas”
• Exclude mentions of “jefferson”
• Narrow down results to where the birthdate is between 1960 and 1970
• Narrow down results to where the profession is either “inventor” or “engineer”

Designing SQL search

In SQL, this could look something like:

"firstName" = 'Thomas' AND
"searchName" ILIKE '%jefferson%' AND
"birthdate" BETWEEN 1960 AND 1970 AND
(
  "profession" = 'inventor' OR
  "profession" = 'engineer'
)

It might not be the most succinct form of SQL to do the job, but it is good enough.

https://contra.com/p/WobOBob7-building-advanced-sql-search-from-a-user-text-input