Finding a row in a text column in MySQL

Question

I have a mysql table in which there is a column that stores xml as a string. I need to find all the tuples where the xml column contains the given 6 character string. 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 in mysql? those. SELECT * FROM items WHERE items.xml [contains the text '123456']

Can this LIKE statement be used?

Answer 1

Perhaps you can use the LIKE / a> clause to do some simple string comparisons:

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

If you need more advanced features, check out MySQL's full-text search functions here: http://dev.mysql.com/doc/refman/5.1/en/fulltext-search.html

Answer 2

Using this may take longer, so use full_text_search

 Select * from items where match(items.xml) against ('your_search_word') 

Answer 3

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

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

Answer 4

you mean:

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

Answer 5

Why not use LIKE?

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