CODEWITHSUNDEEP
mysqlsearchsql-like
user94154
user94154

Reputation: 16564

Search for string within text column in MySQL

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?

Upvotes: 84

Views: 305511

Answers (7)

Marcelo Pacheco
Marcelo Pacheco

Reputation: 276

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,))

Upvotes: 0

Debbie Kurth
Debbie Kurth

Reputation: 481

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);

Upvotes: 1

Raj
Raj

Reputation: 129

Using like might take longer time so use full_text_search:

SELECT * FROM items WHERE MATCH(items.xml) AGAINST ('your_search_word')

Upvotes: 12

rytis
rytis

Reputation: 2721

you mean:

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

Upvotes: 6

Mike Cialowicz
Mike Cialowicz

Reputation: 10020

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

Upvotes: 149

systempuntoout
systempuntoout

Reputation: 74094

Why not use LIKE?

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

Upvotes: 7

Amy B
Amy B

Reputation: 17977

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

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

Upvotes: 6

Related Questions