So I have an awful query that current exists in MS Access that I am trying to rewrite in SQL Server. Basically I get data that comes from a text file that I am trying to filter down based on specific criteria.
My issues comes in with the way the data is in the text file. My table is similar to this:
Table1
BusinessDate DateTime
Amount money
User1 varchar
User2 varchar
User3 varchar
User4 varchar
... varchar
User16 varchar
I have a data table that has the date and then has 16 columns with a data that has been added by a different user. There are some other fields in this table but they are unnecessary for this question.
The current query does filtering on 15 values where the userId is like something.
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 am trying to do is store the like values in a table so I can join on them in my query. I don't know all of the values so I need to use the wildcard because it could be any possible combination of alphanumeric characters. So I will have a table like this:
ValueTable
AB%
CD%
EF%
HI%
...
Then my query would be something similar to this but I don't think this 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 be used because I am totally stumped.
There is no combination of LIKE & IN in SQL, much less in TSQL (SQL Server) or PLSQL (Oracle). Part of the reason for that is because Full Text Search (FTS) is the recommended alternative.
Example - Two Conditions in the WHERE Clause (AND Condition)You can use the AND condition in the WHERE clause to specify more than 1 condition that must be met for the record to be selected.
The LIKE operator is used in a WHERE clause to search for a specified pattern in a column. There are two wildcards often used in conjunction with the LIKE operator: The percent sign (%) represents zero, one, or multiple characters. The underscore sign (_) represents one, single character.
the LIKE operation is not permitted to be used with IN.
SELECT *
FROM Table1
WHERE EXISTS (SELECT *
FROM ValueTable
WHERE User1 Like Value
OR User2 Like Value)
Or (2008 syntax)
SELECT *
FROM Table1
WHERE EXISTS (SELECT *
FROM (VALUES(User1),
(User2),
(User3),
(User4),
/* ... */
(User15),
(User16) ) Users(U)
JOIN ValueTable
ON U Like Value)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With