Search for string within text column in MySQL

mysql search sql-like
user94154 picture user94154 · Mar 26, 2010 · Viewed 230.1k times · Source

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?

Answer

Mike Cialowicz picture Mike Cialowicz · Mar 26, 2010

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

Related questions

MySQL search and replace some text in a field

What MySQL query will do a text search and replace in one particular field in a table? I.e. search for foo and replace with bar so a record with a field with the value hello foo becomes hello bar.

Read More
Fulltext Search with InnoDB

I'm developing a high-volume web application, where part of it is a MySQL database of discussion posts that will need to grow to 20M+ rows, smoothly. I was originally planning on using MyISAM for the tables (for the built-in fulltext …

Read More
Implementation of Levenshtein distance for mysql/fuzzy search?

I would like to be able to search a table as follows for smith as get everything that it within 1 variance. Data: O'Brien Smithe Dolan Smuth Wong Smoth Gunther Smiht I have looked into using Levenshtein distance does anyone know …

Read More