Hi I'm writing MySQL requests for a database that I didn't design, and cannot really change at this point, and I had a question, I have not tested this so it might work as designed but I'm not sure.
So the table I'm searching only have three columns id, title, and description but the description column is actually a list of other information stored in a standardized way (this is for a music metadata so it would look something like this):
id | title | description
1 | stairway_to_heaven | artist=led_zeppelin, genre=rock, album=led_zeppelin_iv
What I want to do is write query that will select all the songs with artist containing the string zeppelin
What I initially came up with is
"SELECT * FROM table WHERE description LIKE %artist=%zeppelin%"
While I believe this should work if the name is present in the description what I believe to be the problem is in the scenario where I want to find all the artists that contain rock it would look like this
"SELECT * FROM table WHERE description LIKE %artist=%rock%"
My first question is this would select the row because rock is in the row after artist= correct?
My second question is would this fix the issue?
"SELECT * FROM table WHERE description LIKE %artist=%rock% AND WHERE description NOT LIKE %artist=%,%rock%"
My thought was that if I omit the Strings that have a , (the delimiter) in between the artist= and the comparison tag it would not choose the above row. My potential problem is that if the delimiter is in the artist name it would fail but I should be able to solve this by choosing a delimiter that isn't in any of the field values and enforcing that this property holds.
Also sorry about any formatting issues this is my first attempt at writing a question.
Thanks, twain249
Do you have experience with Regular expressions? If yes, you can use them in your query to deal with cases like this. See some examples here: http://dev.mysql.com/doc/refman/5.0/en/regexp.html
Alternatively, you can use a "split" function as defined here: http://blog.fedecarg.com/2009/02/22/mysql-split-string-function/