Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL Where Clause with LIKE and IN

Tags:

sql

sql-server

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.

like image 345
Taryn Avatar asked Nov 18 '11 17:11

Taryn


People also ask

Is there a combination of like and in in SQL?

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.

Can I use two and in WHERE clause in SQL?

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.

What is like %% in SQL?

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.

Can we use like with in clause in SQL?

the LIKE operation is not permitted to be used with IN.


1 Answers

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)  
like image 124
Martin Smith Avatar answered Oct 09 '22 22:10

Martin Smith