Combination of 'Like' and 'In' Using T-Sql

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'

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

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

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.

SQL query combination of like and in?

You need to use multiple OR:

select * 
from tableA 
where columnA like '%complete%'
   or columnA like '%requested%'
   or ...

Use Join:

SELECT *
FROM tableA t
JOIN VALUES (('%complete%'), ('%requested%'), ...) v(c)
  ON t.columnA LIKE v.c

Be aware that search pattern %phrase% is not SARG-able and query optimizer won't use index on that column, if exists any.

You should consider usage of Full-Text Search

Using SQL LIKE and IN together

You can do it by in one query by stringing together the individual LIKEs with ORs:

SELECT * FROM tablename
WHERE column LIKE 'M510%'
OR    column LIKE 'M615%'
OR    column LIKE 'M515%'
OR    column LIKE 'M612%';

Just be aware that things like LIKE and per-row functions don't always scale that well. If your table is likely to grow large, you may want to consider adding another column to your table to store the first four characters of the field independently.

This duplicates data but you can guarantee it stays consistent by using insert and update triggers. Then put an index on that new column and your queries become:

SELECT * FROM tablename WHERE newcolumn IN ('M510','M615','M515','M612');

This moves the cost-of-calculation to the point where it's necessary (when the data changes), not every single time you read it. In fact, you could go even further and have your new column as a boolean indicating that it was one of the four special types (if that group of specials will change infrequently). Then the query would be an even faster:

SELECT * FROM tablename WHERE is_special = 1;

This tradeoff of storage requirement for speed is a useful trick for larger databases - generally, disk space is cheap, CPU grunt is precious, and data is read far more often than written. By moving the cost-of-calculation to the write stage, you amortise the cost across all the reads.

SQL LIKE Operator for multiple combinations of string spacing?

declare @Width varchar(10)='6', @Height varchar(10)='4';

select *
from 
(
values 
('HIP 6" X 4" Sign Digital'), ('HIP 6"X4"'), ('HIP 6" X4"'), ('6" X4" Digital'), 
('HIP7 6" X 4"'), ('HIP 6"     X        4" Sign Digital'), ('HIP 6"X       4" Sign Digital'),
('HIP 16" X 4"'), ('HIP 6" X 24"'), ('HIP6 " X 4"')
) as t(description)
where '.' + replace(replace(replace(replace(replace(description,  ' ', '- '), ' -', ''), '- ', ' '), 'X ', 'X'), ' X', 'X') like '%[^0-9]'+@Width+'"X'+@Height+'"%';

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 '/';

Combine LIKE and IN using only WHERE clause

One possible approach is to use EXISTS in combination with LIKE in the subquery:

select * from table1 t1 
where exists (select null 
              from table2 t2 
              where t1.fullname like '%' || t2.pattern || '%');

---

Related Topics