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')? `
This question is related to
mysql
Two options:
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)
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.
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);
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
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
Source: Stackoverflow.com