Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to search for rows containing a substring?

Tags:

mysql

odbc

If I store an HTML TEXTAREA in my ODBC database each time the user submits a form, what's the SELECT statement to retrieve 1) all rows which contain a given sub-string 2) all rows which don't (and is the search case sensitive?)


Edit: if LIKE "%SUBSTRING%" is going to be slow, would it be better to get everything & sort it out in PHP?

like image 805
Mawg says reinstate Monica Avatar asked Nov 08 '10 08:11

Mawg says reinstate Monica


People also ask

How do you check if a row contains a string in pandas?

Using “contains” to Find a Substring in a Pandas DataFrame The contains method returns boolean values for the Series with True for if the original Series value contains the substring and False if not. A basic application of contains should look like Series. str. contains("substring") .

How do you select a row with specific text in Python?

Method 1 : Using contains() Using the contains() function of strings to filter the rows. We are filtering the rows based on the 'Credit-Rating' column of the dataframe by converting it to string followed by the contains method of string class.


1 Answers

Well, you can always try WHERE textcolumn LIKE "%SUBSTRING%" - but this is guaranteed to be pretty slow, as your query can't do an index match because you are looking for characters on the left side.

It depends on the field type - a textarea usually won't be saved as VARCHAR, but rather as (a kind of) TEXT field, so you can use the MATCH AGAINST operator.

To get the columns that don't match, simply put a NOT in front of the like: WHERE textcolumn NOT LIKE "%SUBSTRING%".

Whether the search is case-sensitive or not depends on how you stock the data, especially what COLLATION you use. By default, the search will be case-insensitive.

Updated answer to reflect question update:

I say that doing a WHERE field LIKE "%value%" is slower than WHERE field LIKE "value%" if the column field has an index, but this is still considerably faster than getting all values and having your application filter. Both scenario's:

1/ If you do SELECT field FROM table WHERE field LIKE "%value%", MySQL will scan the entire table, and only send the fields containing "value".

2/ If you do SELECT field FROM table and then have your application (in your case PHP) filter only the rows with "value" in it, MySQL will also scan the entire table, but send all the fields to PHP, which then has to do additional work. This is much slower than case #1.

Solution: Please do use the WHERE clause, and use EXPLAIN to see the performance.

like image 146
Konerak Avatar answered Sep 23 '22 22:09

Konerak