Search for string within text column in MySQL
MysqlSearchSql LikeMysql Problem Overview
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?
Mysql Solutions
Solution 1 - Mysql
You could probably use the http://dev.mysql.com/doc/refman/5.0/en/string-comparison-functions.html">`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">http://dev.mysql.com/doc/refman/5.1/en/fulltext-search.html</a>
Solution 2 - Mysql
Using like might take longer time so use full_text_search:
SELECT * FROM items WHERE MATCH(items.xml) AGAINST ('your_search_word')
Solution 3 - Mysql
SELECT * FROM items WHERE `items.xml` LIKE '%123456%'
The %
operator in LIKE
means "anything can be here".
Solution 4 - Mysql
Why not use LIKE?
SELECT * FROM items WHERE items.xml LIKE '%123456%'
Solution 5 - Mysql
you mean:
SELECT * FROM items WHERE items.xml LIKE '%123456%'
Solution 6 - Mysql
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);