BAH! Sometimes I hate MySQL with a passion.

So, I have a series of strings in a table that all have a word or substring quoted inside of them. For example:

1) I "love" MySQL.
2) MySQL can burn in the "fiery" pits of Hell.

Now, I want to pull back the quoted strings with a simple SQL script … to do that fast (it doesn’t have to be clean ATM), I thought I would use SUBSTRING and LOCATE … I would LOCATE the position of the first quote and then do another LOCATE on the substring starting at that position and then by using SUBSTRING on the outside of that block I could get the enclosed string… Sounds simple huh?

So, I test out my LOCATE commands to make sure I can actually get the correct positions:

SELECT
  LOCATE('"', String)+1 AS 'First Pos', 
  LOCATE('"', String, LOCATE('"', String)+1) AS 'Last Pos'FROM
  Table;

This would return 4 (I add one to the result to get the first character after the quote) and 8 for the first string we parse.

Now that I have my position finding statements debugged, I set about pulling a SUBSTRING with these numbers. Now SUBSTRING takes up to three variables. The String to be cut, the starting position, and the length of the cut.

So to use it, I would create a function like SUBSTRING (which happens to work great when tested), or in this case:

SUBSTRING(String, LOCATE('"', String)+1, LOCATE('"', String, 
LOCATE('"', String)+1)-(LOCATE('"', String)+1))

But of course this doesn’t work…

#@*%!^%

It appears that MySQL has an issue parsing the sub arithmatic and string functions inside of the SUBSTRING command. So, while it worked fine with the hard-coded numbers, it bombs out when the numbers are dynmically generated.

How frustrating.