SQL query find few strings in diferent columns in a table row (restrictive)

I have a table like this one (in a SQL SERVER):

field_name	field_descriptor	tag1	tag2	tag3	tag4	tag5
house	your home	home	house	null	null	null
car	first car	car	wheel	null	null	null
...	...	...	...	...	...	...

I'm developing a WIKI with a searchbar, which should be able to handle a query with more than one string for search. As an user enters a second string (spaced) the query should be able to return results that match restrictively the two strings (if exists) in any column, and so with a three string search.

Easy to do for one string with a simple SELECT with ORs.

Tried in the fronted in JS with libraries like match-sorter but it's heavy with a table with more than 100,000 results and more in the future.

I thought the query should do the heavy work, but maybe there is no simple way doing it.

Thanks in advance!

Tried to do the heavy work with all results in frontend with filtering and other libraries like match-sorter. Works but take several seconds and blocks the front.
Tried to create a simple OR/AND query but the posibilities with 3 search-strings (could be 1, 2 or 3) matching any column to any other possibility is overwhelming.

Solution

You can use STRING_SPLIT to get a separate row per search word from the search words string. Then only select rows where all search words have a match.

The query should look like this:

select *
from mytable t
where exists
(
  select null
  from (select value from string_split(@search, ' ')) search
  having min(case when search.value in (t.tag1, t.tag2, t.tag3, t.tag4, t.tag5) then 1 else 0 end) = 1
);

Unfortunately, SQL Server seems to have a flaw (or even a bug) here and reports:

Msg 8124 Level 16 State 1 Line 8 Multiple columns are specified in an aggregated expression containing an outer reference. If an expression being aggregated contains an outer reference, then that outer reference must be the only column referenced in the expression.

Demo: https://dbfiddle.uk/kNL1PVOZ

I don't have more time at hand right now, so you may use this query as a starting point to get the final query.