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 query string contains using INSTRINSTR(str, substr) function returns the index of the first occurrence of the substring passed in as parameters. Here str is the string passed in as the first argument, and substr is the substring passed in as the second argument.
MySQL LOCATE() Function The LOCATE() function returns the position of the first occurrence of a substring in a string. If the substring is not found within the original string, this function returns 0. This function performs a case-insensitive search. Note: This function is equal to the POSITION() function.
There is a Schemas tab on the side menu bar, click on the Schemas tab, then double click on a database to select the database you want to search. Then go to menu Database - Search Data, and enter the text you are searching for, click on Start Search.
Full-Text Search in MySQL server lets users run full-text queries against character-based data in MySQL tables. You must create a full-text index on the table before you run full-text queries on a table. The full-text index can include one or more character-based columns in the table.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With