SQL Server, Combining Like and In

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 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.

Is there any way to combine LIKE and IN in SQL?

You cannot use LIKE IN but you can use OR:

select MAX(score) as score, title from 
(
  select LEN(CurrentTerm) as score, title 
  from tableName 
  WHERE title LIKE '%railway employee%'
    OR title LIKE '%railway%'
    OR title LIKE '%employee%'
) as t1
group by title
order by score DESC;

You might be able to use something similar to the following which uses a derived table of the 3 search terms with a score value:

select max(score) as score, title
from
(
  select 2 score, 'railway employee' term union all
  select 1 score, 'railway' term union all
  select 1 score, 'employee' term 
) d
inner join tableName t
  on title like concat('%', term, '%') 
group by title
order by score desc;

See SQL Fiddle with Demo

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 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 + '%')

SQL combine LIKE & AND & =

the point of this line is to choose everything in the column from the database which is why I choose all the letters (a-z).

var getData = "SELECT * FROM Test WHERE exercise = " + exercise + " AND exerVariName LIKE '[A-Z]%' AND date >= '" + fromDate + "' AND date <= '" + toDate + "'";

I think you are overthinking this. If you want everything (ie. no filter on the column) then omit that part/filter on the clause completely.

"SELECT * FROM Test WHERE exercise = " + exercise + " AND date >= '" + fromDate + "' AND date <= '" + toDate + "'";

Finally this is vulnerable to sql injection attacks, you should use parameterized statements instead of string concatenation. If you update your question with the language I might be able to procure an example of a parameterized query in that language.

How to use LIKE and NOT LIKE together in a SQL Server query

All of the strings have either an L or P, which is what %[LP]% looks for.

One way is to escape the pattern:

SELECT TOP (15) * 
FROM [Users] 
WHERE [codename] LIKE '%Luis%' AND
      [codename] NOT LIKE '%/[LP/]%' escape '/';

Using LIKE in SQL with multiple search terms

You want AND, not OR:

SELECT columnName 
FROM TableName 
WHERE columnName NOT LIKE '%stack%' AND columnName NOT LIKE '%overflow%'

You can expand the WHERE clause with more conditions as needed.

Side note: if you know in advance in which sequence the words appear in the string, you can also do:

SELECT columnName 
FROM TableName 
WHERE columnName NOT LIKE '%stack%overflow%'

---

Related Topics