I used Google Site Search (https://www.google.com/work/search/products/gss.html), which starts at $100 per year. Outsource all that infrastructure to a company who's good at it, park your static HTML on a CDN, and enjoy fast worldwide access with searchable content.
That's not the only option, but it's a boss-friendly company to name drop. Most organizations wouldn't blink at the price, especially if it means you can move off dynamic hosting to far cheaper static hosting.
A simple SQL query is not a great solution even for dynamic sites that pull content from SQL databases. You have to protect against SQL injection and you usually will want fulltext indices for all the text fields to be searched. It's also going to be slow in the naive implementation for databases of any significant size.
The better solution for both dynamic and static sites is to set up a search appliance like elasticsearch, solr, or algolia. You can use JS to query it and still be static on the server.
If you do set up your own, remember to use a reverse proxy like nginx to avoid exposing elasticsearch directly to the internet.
That's not the only option, but it's a boss-friendly company to name drop. Most organizations wouldn't blink at the price, especially if it means you can move off dynamic hosting to far cheaper static hosting.