How to search for rows containing a substring

Question

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  amp  sort it out in PHP

User · Accepted Answer

Well  you can always try WHERE textcolumn LIKE  quot  SUBSTRING  quot  - 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  quot  SUBSTRING  quot   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  quot  value  quot  is slower than WHERE field LIKE  quot value  quot  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  quot  value  quot   MySQL will scan the entire table  and only send the fields containing  quot value quot   2  If you do SELECT field FROM table and then have your application  in your case PHP  filter only the rows with  quot value quot  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

User · Answer

Info on MySQL s full text search  This is restricted to MyISAM tables  so may not be suitable if you wantto use a different table type   http   dev mysql com doc refman 5 0 en fulltext-search html  Even if WHERE textcolumn LIKE   SUBSTRING   is going to be slow  I think it is probably better to let the Database handle it rather than have PHP handle it  If it is possible to restrict searches by some other criteria  date range  user  etc  then you may find the substring search is OK  ish     If you are searching for whole words  you could pull out all the individual words into a separate table and use that to restrict the substring search   So when searching for  my search string  you look for the the longest word  search  only do the substring search on records containing the word  search

[mysql] How to search for rows containing a substring?

Updated answer to reflect question update:

Examples related to mysql

Examples related to odbc