19

I must use this format where A operand B. A is the field; I want B to be either "text 1" or "text 2", so if A has data like "text 1 texttext" or "texttext 2" , the query will have result.

But how do I write this? Does MySQL support something like

where A contains ('text 1' OR 'text 2')? `
Hao
  • 6,291
  • 9
  • 39
  • 88

4 Answers4

43

Two options:

  1. Use the LIKE keyword, along with percent signs in the string

    select * from table where field like '%a%' or field like '%b%'.

    (note: If your search string contains percent signs, you'll need to escape them)

  2. If you're looking for more a complex combination of strings than you've specified in your example, you could regular expressions (regex):

    See the MySQL manual for more on how to use them: http://dev.mysql.com/doc/refman/5.1/en/regexp.html

Of these, using LIKE is the most usual solution -- it's standard SQL, and in common use. Regex is less commonly used but much more powerful.

Note that whichever option you go with, you need to be aware of possible performance implications. Searching for sub-strings like this will mean that the query will have to scan the entire table. If you have a large table, this could make for a very slow query, and no amount of indexing is going to help.

If this is an issue for you, and you'r going to need to search for the same things over and over, you may prefer to do something like adding a flag field to the table which specifies that the string field contains the relevant sub-strings. If you keep this flag field up-to-date when you insert of update a record, you could simply query the flag when you want to search. This can be indexed, and would make your query much much quicker. Whether it's worth the effort to do that is up to you, it'll depend on how bad the performance is using LIKE.

Suchit kumar
  • 11,809
  • 3
  • 22
  • 44
Spudley
  • 166,037
  • 39
  • 233
  • 307
  • 1
    Just be careful when using the 'or' statement. It's best to use parentheses around them such as `select * from table where (field like '%a%' or field like '%b%')`. If you have other `AND` clauses your query might act like `SELECT * FROM TABLE WHERE (A = 1 AND B =2 AND C = 3) OR (D =4)` which is usually not what you want. – Maximus Nov 17 '15 at 22:41
  • For my part, in light of the question's use of A and B, it would've been a bit clearer if you had said %text 1% instead of %a% – donutguy640 Apr 05 '19 at 18:16
7

You can write your query like so:

SELECT * FROM MyTable WHERE (A LIKE '%text1%' OR A LIKE '%text2%')

The % is a wildcard, meaning that it searches for all rows where column A contains either text1 or text2

alex
  • 3,710
  • 32
  • 44
  • but I have to use the format `A operand B` . This is all in all 3 variables actually that I have to use due to programing –  May 25 '11 at 08:24
  • 2
    I'm honestly not sure that this is possible within your constraints... when we are comparing for two completely different patterns we should compare for them separately, and the boolean OR exists so that we can do this. I'm not aware of any MySQL comparison that allows us to do a comparison on either of two patterns with only one operator. – jcrawfordor May 25 '11 at 08:42
0

I've used most of the times the LIKE option and it works just fine. I just like to share one of my latest experiences where I used INSTR function. Regardless of the reasons that made me consider this options, what's important here is that the use is similar: instr(A, 'text 1') > 0 or instr(A, 'text 2') > 0 Another option could be: (instr(A, 'text 1') + instr(A, 'text 2')) > 0

I'd go with the LIKE '%text1%' OR LIKE '%text2%' option... if not hope this other option helps

tanovellino
  • 73
  • 3
0

I user for searching the size of motorcycle :

For example : Data = "Tire cycle size 70 / 90 - 16"

i can search with "70 90 16"

$searchTerms = preg_split("/[\s,-\/?!]+/", $itemName);

foreach ($searchTerms as $term) {
        $term = trim($term);
            if (!empty($term)) {
            $searchTermBits[] = "name LIKE '%$term%'";
            }
        }

$query = "SELECT * FROM item WHERE " .implode(' AND ', $searchTermBits);
Oscar Fernando
  • 1
  • 1