How to Use Like and in for a Where Statment

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

how to use like and between in where clause in same statement?

Since your date values also include time, you can't use BETWEEN. The only safe way to do this is:

SELECT <cols> FROM dbo.table_name
WHERE CONVERT(DATE, fromDate, 103) >= '20120612' 
AND CONVERT(DATE, fromDate, 103) < '20120617';

But as Martin noticed, you'll never be able to use an index on that column, so this will always perform a full table scan.

If you really, really want to use BETWEEN, converting to DATE is the only safe way to do so (well, or trimming the time off in other, less efficient ways):

SELECT <cols> FROM dbo.table_name
WHERE CONVERT(DATE, fromDate, 103) BETWEEN '20120612' AND '20120616';

But for consistency reasons I recommend against between even in that case.

Using 'LIKE' with an 'IN' clause full of strings

put the values into a table and use a join rather than an in clause:

SELECT * FROM emailaddresses as ea
INNER JOIN addresses as a
ON ea.address like '%' + a.address + '%'

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

Multiple LIKE statements in SQL

Yes, you can do the following:

SELECT * FROM stores WHERE store_name LIKE '%$user_input%' 
AND store_name LIKE '%Walmart%'
ORDER BY store_name ASC

How do you OR two LIKE statements?

SELECT col FROM db.tbl WHERE (col LIKE 'str1' OR col LIKE 'str2') AND col2 = num

SQL 'LIKE' query using '%' where the search criteria contains '%'

If you want a % symbol in search_criteria to be treated as a literal character rather than as a wildcard, escape it to [%]

... where name like '%' + replace(search_criteria, '%', '[%]') + '%'

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

How to use bindValue with LIKE operator in SQL query?

You probably want :

$query = "SELECT * 
          FROM books
          WHERE title LIKE CONCAT( '%', :title, '%')";
...
...
statement->bindValue(':title', $title, PDO::PARAM_STR);

The bind parameter should be used as a litteral string. CONCAT can be used to concatenate the parameter with percent signs on both ends.

---

Related Topics