Combining "Like" and "In" For SQL Server

Combining LIKE and IN for SQL Server

Effectively, the IN statement creates a series of OR statements... so

SELECT * FROM table WHERE column IN (1, 2, 3)

Is effectively

SELECT * FROM table WHERE column = 1 OR column = 2 OR column = 3

And sadly, that is the route you'll have to take with your LIKE statements

SELECT * FROM table
WHERE column LIKE 'Text%' OR column LIKE 'Hello%' OR column LIKE 'That%'

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.

Both Oracle and SQL Server FTS implementations support the CONTAINS keyword, but the syntax is still slightly different:

Oracle:

WHERE CONTAINS(t.something, 'bla OR foo OR batz', 1) > 0

SQL Server:

WHERE CONTAINS(t.something, '"bla*" OR "foo*" OR "batz*"')

The column you are querying must be full-text indexed.

Reference:

• Building Full-Text Search Applications with Oracle Text
• Understanding SQL Server Full-Text

SQL: Combining LIKE and IN in SQL , and Showing the Array of LIKE in a column

One solution would be to convert your WHERE EXISTS clause to an INNER JOIN, like :

SELECT t1.file_id, vals.val
FROM
    table1 t1
    INNER JOIN  (VALUES ('value1'), ('value2'), ('value3')) Vals(val) 
        ON  t1.column1 LIKE '%' + vals.val + '%' 

The downside of this approach is that if multiple expression match the value of column1 in a given record, you will find two rows in the output. To avoid that, you could for example concatenate all succesfully matched expressions in a single field.

If you are using SQL Server 2017 (your query runs fine on this RDBMS), you can use STRING_AGG :

SELECT t1.file_id, STRING_AGG(vals.val, ',')
FROM   
    table1 t1
    INNER JOIN  (VALUES ('value1'), ('value2'), ('value3')) Vals(val) 
        ON  t1.column1 LIKE '%' + vals.val + '%' 
GROUP BY t1.file_id

Tested in this db fiddle.

SQL Server, combining LIKE and IN?

Not really.

There is no alternation operator in the LIKE pattern syntax. If on 2008 you can use

SELECT *
FROM   table1
WHERE  EXISTS(SELECT *
              FROM   (VALUES ('value1'),
                             ('value2'),
                             ('value3')) Vals(val)
              WHERE  column1 LIKE '%' + val + '%')  

You can also use Regular Expressions in SQL Server but not natively. You need to enable CLR and install an assembly for this.

Combination of 'LIKE' and 'IN' using t-sql

There is no combined LIKE and IN syntax but you can use LIKE to JOIN onto your query as below.

;WITH Query(Result) As
(
SELECT '% Main Street' UNION ALL
SELECT 'foo %'
)
SELECT DISTINCT s.* 
FROM Street s
JOIN Query q ON StreetName LIKE q.Result

Or to use your example in the comments

SELECT DISTINCT s.* 
FROM Street s
JOIN CarStreets cs ON s.StreetName LIKE cs.name + '%'
WHERE cs.Streets = 'offroad'

SQL Server : combining like rows

When you use GROUP BY, it means you want to group like elements together and present some sort of aggregate (COUNT, SUM, MIN, etc.) of the remaining items you want to display. Becaus eof this, if you include [WL].[Id] in the select list, you'll basically get the full selection without any aggregation (since [WL].[Id] is unique).

You'll need to do something like the following:

SELECT MIN([WL].[Id]) AS ID
  ,[WL].[UserId]
  ,[U].[Id]
  ,[U].[UserName]
  ,SUM([SLength]) AS SLength
FROM [Wsite].[dbo].[WLog] as WL 
INNER JOIN [Wsite].[dbo].[Users] AS U 
        ON [U].[Id] = [WL].[UserId] 
     WHERE [WL].[WDate] >= CONVERT(datetime, '2012-01-01 00:00:00', 120)  
       AND [WL].[WDate] <= CONVERT(datetime, GETDATE(), 120)
  GROUP BY [WL].[UserId]
  ,[U].[Id]
  ,[U].[UserName]

(For brevity, I did not include all the fields).

Also note that since [WL].[Id] appears to be unique, it is meaningless in this context, unless you want to display its first occurance (then you can add MIN([WL].[Id]) to the SELECT list).

SQL Multiple LIKE Statements

WITH CTE AS
(
SELECT VALUE
FROM (
        VALUES ('B79'), ('BB1'), ('BB10'), ('BB11'), ('BB12'), ('BB18'), ('BB2'), ('BB3'), ('BB4'), ('BB5'), ('BB6'), ('BB8'), ('BB9'), ('BB94'), ('BD1'), ('BD10'), ('BD11'), ('BD12'), ('BD13'), ('BD14'),
                ('BD15'), ('BD16'), ('BD17'), ('BD18'), ('BD19'), ('BD2'), ('BD20'), ('BD21'), ('BD22'), ('BD3'), ('BD4'), ('BD5'), ('BD6')
     ) V(VALUE)
)   
SELECT * 
FROM tbl_ClientFile T
WHERE EXISTS ( SELECT TOP 1 1 FROM CTE WHERE T.CLNTPOST1 LIKE CTE.VALUE + '%')

Combining LIKE with IN in SQL

Might be too specific to your example, but you could do LIKE '[a-c]%'. Other than that, I'm not aware of any LIKE-like IN syntax

---

Related Topics