73

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
  • 15,673
  • 14
  • 84
  • 131
user94154
  • 15,564
  • 19
  • 75
  • 115
  • Refer this: http://winashwin.wordpress.com/2012/08/28/mysql-search/ –  Aug 30 '12 at 09:25

6 Answers6

129

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,664
  • 9
  • 48
  • 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
12

Using like might take longer time so use full_text_search:

SELECT * FROM items WHERE MATCH(items.xml) AGAINST ('your_search_word')
simhumileco
  • 27,137
  • 16
  • 123
  • 105
Raj
  • 121
  • 1
  • 2
  • 11
    Is this proven to be faster than LIKE ? – Tim Baas Apr 03 '13 at 18:48
  • 4
    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
  • 1
    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
6
SELECT * FROM items WHERE `items.xml` LIKE '%123456%'

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

Amy B
  • 17,636
  • 12
  • 62
  • 81
5

Why not use LIKE?

SELECT * FROM items WHERE items.xml LIKE '%123456%'
systempuntoout
  • 69,075
  • 45
  • 164
  • 239
4

you mean:

SELECT * FROM items WHERE items.xml LIKE '%123456%'
Oliver M Grech
  • 2,854
  • 1
  • 19
  • 31
rytis
  • 2,609
  • 21
  • 26
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
  • 357
  • 2
  • 14