79

I have mysql table that has a column that stores xml as a string. I need to find all tuples where the xml column contains a given string of 6 characters. Nothing else matters--all I need to know is if this 6 character string is there or not.

So it probably doesn't matter that the text is formatted as xml.

Question: how can I search within mysql? ie SELECT * FROM items WHERE items.xml [contains the text '123456']

Is there a way I can use the LIKE operator to do this?

MPelletier
  • 16,256
  • 15
  • 86
  • 137
user94154
  • 16,176
  • 20
  • 77
  • 116
  • Refer this: http://winashwin.wordpress.com/2012/08/28/mysql-search/ –  Aug 30 '12 at 09:25

7 Answers7

143

You could probably use the LIKE clause to do some simple string matching:

SELECT * FROM items WHERE items.xml LIKE '%123456%'

If you need more advanced functionality, take a look at MySQL's fulltext-search functions here: http://dev.mysql.com/doc/refman/5.1/en/fulltext-search.html

Mike Cialowicz
  • 9,892
  • 9
  • 47
  • 76
  • 1
    .. how would I modify this to use with a PHP query? I have a string `$message` and I'd like to select the row with that exact string. I have `SELECT * FROM Messages WHERE from_id = '$fromID' AND to_id = '$toID' AND message LIKE '$message'` but I don't think that works – Hristo Jun 30 '10 at 15:32
  • What if `123456` is the beginning of the string, then it wouldn't work. – tmarois Oct 11 '18 at 19:15
  • 1
    timothymarois - the % matches zero or more characters, so it will work if the desired string is at the beginning, end, or middle of what you're searching in. See: https://www.w3schools.com/sql/sql_like.asp – LConrad Dec 20 '18 at 20:09
  • You should probably [escape it](https://stackoverflow.com/questions/7327092/how-should-i-escape-characters-inside-this-like-query) too. – c00000fd Dec 25 '19 at 09:02
11

Using like might take longer time so use full_text_search:

SELECT * FROM items WHERE MATCH(items.xml) AGAINST ('your_search_word')
simhumileco
  • 31,877
  • 16
  • 137
  • 115
Raj
  • 119
  • 1
  • 2
  • 11
    Is this proven to be faster than LIKE ? – Tim Baas Apr 03 '13 at 18:48
  • 5
    It is important to note that for this to work one needs to have `FULLTEXT` index on the target column. – stamster Dec 03 '18 at 15:01
  • 2
    To further expand for others, FTS is very much so different than `LIKE` which will return variants of words and phrases, while FTS will not. eg. `LIKE '%123456%'` will return results with `"0123456", "123456", "1234567"`, etc. Where `AGAINST('123456')` will only return results as single words to *match against* such as `"is 123456", "123456", "123456 is"`, etc but *NOT* find `"0123456"` or `"1234567"`. Example: https://www.db-fiddle.com/f/5rJUTdDtV63RybnY4U9L2W/0 Which is why FTS can perform faster on larger datasets, as it is not performing a full table scan that occurs with `LIKE`. – Will B. Mar 31 '20 at 01:20
  • This fails if there isn't a full text index against the column. – Marcelo Pacheco Jul 05 '23 at 14:37
6
SELECT * FROM items WHERE `items.xml` LIKE '%123456%'

The % operator in LIKE means "anything can be here".

Amy B
  • 17,874
  • 12
  • 64
  • 83
6

you mean:

SELECT * FROM items WHERE items.xml LIKE '%123456%'
Oliver M Grech
  • 3,071
  • 1
  • 21
  • 36
rytis
  • 2,649
  • 22
  • 27
6

Why not use LIKE?

SELECT * FROM items WHERE items.xml LIKE '%123456%'
systempuntoout
  • 71,966
  • 47
  • 171
  • 241
1

When you are using the wordpress prepare line, the above solutions do not work. This is the solution I used:

   $Table_Name    = $wpdb->prefix.'tablename';
   $SearchField = '%'. $YourVariable . '%';   
   $sql_query     = $wpdb->prepare("SELECT * FROM $Table_Name WHERE ColumnName LIKE %s", $SearchField) ;
 $rows = $wpdb->get_results($sql_query, ARRAY_A);
Debbie Kurth
  • 403
  • 3
  • 16
0

Try function LOCATE. Better than LIKE since you don't need to massage the argument. Example (python):

cursor.execute('select columns from table where locate(%s,haystack)>0', (needle,))
Marcelo Pacheco
  • 152
  • 1
  • 5