SQL Where Clause with LIKE and IN

Question

So, I have a terrible query that exists in MS Access, which I am trying to rewrite in SQL Server. Basically, I get data that comes from a text file, which I am trying to filter based on certain criteria.

My problems are with the way the data is in a text file. My table looks like this:

Table1 BusinessDate DateTime Amount money User1 varchar User2 varchar User3 varchar User4 varchar ... varchar User16 varchar 

I have a data table that has a date, and then has 16 columns with data that was added by another user. There are other fields in this table, but they are not needed for this question.

The current query is filtering by 15 values, where userId is somehow similar.

 SELECT * FROM Table1 WHERE (User1 Like 'AB%' Or User1 Like 'CD%' Or User1 Like 'EF%'...) OR (User2 Like 'AB%' Or User2 Like 'CD%' Or User2 Like 'EF%'...) 

What I'm trying to do is keep the same values ​​in the table so that I can join them in my query. I do not know all the values, so I need to use a wildcard, because it can be any possible combination of alphanumeric characters. So I will have a table like this:

 ValueTable AB% CD% EF% HI% ... 

Then my request will look like this, but I do not think it is possible

 SELECT * FROM Table1 WHERE User1 Like IN (SELECT Value FROM ValueTable) OR User2 Like IN (SELECT Value FROM ValueTable) 

Is it possible to do something like this? If so, what syntax should I use because I'm completely at a dead end.

Answer 1

 SELECT * FROM Table1 WHERE EXISTS (SELECT * FROM ValueTable WHERE User1 Like Value OR User2 Like Value) 

Or (syntax 2008)

 SELECT * FROM Table1 WHERE EXISTS (SELECT * FROM (VALUES(User1), (User2), (User3), (User4), /* ... */ (User15), (User16) ) Users(U) JOIN ValueTable ON U Like Value)