CODEWITHSUNDEEP
mysqlsqldatabase
Dave
Dave

Reputation: 11

How do you use OR LIKE in a mysql select statement?

I'm having trouble with this select statement:

Select * from table where row != '' and row2 like '%test%' or like '%test2%';

The "or like" part isn't working, I'm guessing I'm using it incorrectly.

Upvotes: 1

Views: 1212

Answers (4)

Jonathan Leffler
Jonathan Leffler

Reputation: 754400

There is an operator precedence problem - use parentheses to resolve it:

SELECT *
  FROM table
 WHERE row != '' 
   AND (row2 LIKE '%test%' OR row2 LIKE '%test2%');

You were also missing the second 'row2' in the OR'd conditions.

Of course, the second LIKE condition in the example will not return any rows that the first does not (because every row that contains 'test2' also contains 'test'), but we can assume that was an over-simplification for the purposes of the question.

Upvotes: 1

vcsjones
vcsjones

Reputation: 141668

You would do something like this:

SELECT * FROM table
WHERE row != '' AND (row2 like '%test%' OR row2 like '%test2%')

Each OR / AND operate on one thing. As others have noted, order of operations are important. You should group expressions using () so that they are evaluated first.

Upvotes: 7

RRStoyanov
RRStoyanov

Reputation: 1172

You miss the "row2" in second like, but also your query like now works like this

Select * from table where row != '' and row2 like '%test%';
Select * from table where row2 like '%test2%';

If you need it like that, it's ok, but I presume you want in both searches the row to be != ""?

If that's the case, than your query should like this:

Select * from table where row != '' and (row2 like '%test%' or row2 like '%test2%');

notice the brackets.

Upvotes: 1

DRapp
DRapp

Reputation: 48149

Where clauses are associated to columns OF a row in a table you are querying from, not a "row" itself. So, if dealing with a simple customer table, you could apply LIKE queries something like

select 
      lastname,
      firstname,
      address
   from
      customers
   where
         firstname like 'Bill%'
      OR lastname like '%mar%'

will return any names where the first name would specifically START with "Bill" regardless of what it ends with (via the "%" wildcard)

OR

the last name has the value "mar" anywhere in the last name, such as "Marcus", "Tamara", "Omar"...

Each "LIKE" clause being applied is based on the column you are interested in comparing against.... If you wanted multiple values tested for a single column you would have to write it each time...

where
   firstname like 'Bill%'
   or firstname like 'John%'
   or firstname like '%any%'

Upvotes: 0

Related Questions