Menu
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')? `
602
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.
MySQL allows you to specify multiple WHERE clauses. These clauses may be used in two ways: as AND clauses or as OR clauses. What is Operator? An operator is a special keyword used to join or change clauses within a WHERE clause.
MySQL query string contains using INSTR INSTR(str, substr) function returns the index of the first occurrence of the substring passed in as parameters. Here str is the string passed in as the first argument, and substr is the substring passed in as the second argument.
SQL pattern matching allows you to search for patterns in data if you don't know the exact word or phrase you are seeking. This kind of SQL query uses wildcard characters to match a pattern, rather than specifying it exactly. For example, you can use the wildcard "C%" to match any string beginning with a capital C.
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.
60
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
42
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
