mySql retrieving data between square brackets

Question

I have strings of data in a field named content, one record may look something like:

loads of text ... [attr1] some text [attr2] more text [attr3] more text etc...

What I'm looking to do is get all the text within the square brackets; so that I can put it into a PHP array. Is this even possible with mySql?

I've seen the following post: Looking to extract data between parentheses in a string via MYSQL, but they are looking to only extract one value from between their parentheses, I have an unknown number of them. After reading that post I've though of doing something like the following;

SELECT substr(content,instr(content,"["), instr(content,"]")) as attrList from myTable

Which would grab me the following:

[attr1] some text [attr2] some more text [attr3]

and I can use PHP to strip the rest of the text out and then explode the string into an array, but is there a better way to do this just using mySql where I can retrieve something like:

[attr1][attr2][attr3]

I was thinking perhaps regex, but I see that just returns a true of false which doesn't help me a lot.

PostgreSQL can do that I have heard. For MySQL I think there is some user procedure you can use for that. I don’t recall exactly now though. — , Oct 25 '12 at 14:05
No this is best done client side (i.e. in PHP). Multiple regex matches per line can be problematic and even more so if multiline input is supplied. Also it is not a good idea to send lots of data to SQL especially "loads of text" as there are network packet/command processing limits. You can invest in MySQL functions/modules/stored-proc on server side but this can end up making application more complex to deploy/setup/maintain. There is usually good methods for working with client side code, not with MySQL server side custom code. — Darryl Miles, Oct 25 '12 at 14:07
Thanks, I can't switch databases unfortunately and @DarrylMiles I thought that might be the case, I thought I'd ask just on the off chance! — Stu, Oct 25 '12 at 14:10

score 0 · Accepted Answer · edited May 23 '17 at 12:03

After even more research, I'm not sure it's possible in mySql, and I might need the results in string or array form depending on where I'm using them in my app.

So I've created a new method to return the list after I've got the data from the database (with a little help from this post: PHP: Capturing text between square brackets):

public function attrList($array=false)
{
    preg_match_all("/\[.*?\]/",$this->content,$matches);

    $params = str_replace(array('[',']'),'',$matches[0]);

    return ($array===false) ? implode(', ',$params) : $params;
}

mySql retrieving data between square brackets

1 Answers1