MySQL help - isolating a substring
 

Working directly on the database via phpmyadmin, how do I extract "XXXX" from the following string. I will store "XXXX" in a new field:

String:

abcabcabcabc[pic]XXXX[/pic]abcabcabcabc


the "abc..." bits are variable, but there is always one instance of [pic] and [/pic].

I'm guessing that I need something that creates a substring after "[pic]" and before "[/pic]" - but MySQL knowledge is not too great yet :ermm:

I've had a look at www.mysql.com but can't find the answer.

Thanks for any help.

Morag.

Try this:
SELECT SUBSTRING('abcabcabcabc[pic]XXXX[/pic]abcabcabcabc', LOCATE('[pic]', 'abcabcabcabc[pic]XXXX[/pic]abcabcabcabc') + 5, LOCATE('[/pic]', 'abcabcabcabc[pic]XXXX[/pic]abcabcabcabc') - LOCATE('[pic]', 'abcabcabcabc[pic]XXXX[/pic]abcabcabcabc') -5) AS pic

Admin Note: SQL-Tag is buggy!