CODEWITHSUNDEEP
mysqlregexsubstr
NaturalBornCamper
NaturalBornCamper

Reputation: 3876

MySQL extract several substrings in SELECT, an arbitrary number of times

So the previous coder of my project decided to put PHP-seralized info in the MySQL database like such:

a:2:{s:6:"Format";s:4:"NTSC";s:7:"License";s:4:"Home";}

Or

a:1:{s:4:"Size";s:1:"S";}

And I need to extract using MySQL directly what's between the quotes, to display this:

Format: NTSC, License: Home

Or

Size: S

So I need to basically keep what's between the quotes and concatenate it. I could use something like Looking to extract data between parentheses in a string via MYSQL or MySQL: Use REGEX to extract string (select REGEX) But I can't because the number of attributes is arbitrary (0,1,2,3,...) instead of always 2 for example.

Any ways to do this?

Upvotes: 0

Views: 92

Answers (2)

blackpen
blackpen

Reputation: 2424

Unserialize in PHP: Best option is just to unserialize it, concatenate it and hand it to your client (For performance you would narrow down the rows before bringing them to PHP).

<?php
$data='a:2:{s:6:"Format";s:4:"NTSC";s:7:"License";s:4:"Home";}';

/* To unserialize and print */
$arr=unserialize($data); $out="";
foreach ($arr as $k => $val) {
    $out.=$k.":".$val." ";
}
print "$out<br>";
?>

Pattern Matching in PHP: Next option is to regex match it in PHP (There isn't much benefit of doing so though. The unserialize is better). I will leave this example here, just in case if you want to do something with regular expression matches.

<?php

$data='a:2:{s:6:"Format";s:4:"NTSC";s:7:"License";s:4:"Home";}';
preg_match_all('/"(.*?)"/',$data, $result);

$out="";
for ($i = 0; $i < count($result[0]); $i+=2) {
    print $result[1][$i].":".$result[1][$i+1]." ";
}
print "$out<br>";
?>

Export,Process,Import: If the table column is no more being written in the serialized format, and if it is only being read (to get back the legacy data), you could export it as csv file, process the data in perl, add a new column to contain the "Tag=Value" format separated by blanks, and imported the file back into database with a new column in which the new data is inserted. If you need it, I could supply you with a perl script to parse/convert the serialized data.

Pattern Matching in MySql using UDFs: If, on other hand, if you just want to use regular expressions in MySql query itself (to extract the tag/values), you can do so by using this extension. It needs to be build from sources, installed.

In response to question about RLIKE: If you know all possible formats of your serialized data (if it looks simple enough for you), you could run regular expression matches before bringing the rows into PHP (if performance matters).

create table tt (data varchar(100));
insert into tt values ("a:2:{s:6:\"Format\";s:4:\"NTSC\";s:7:\"License\";s:4:\"Home\";}");

select data from tt where data rlike '"Format";s:[0-9]+:"NTSC"';

Upvotes: 1

Rick James
Rick James

Reputation: 142366

Write a PHP script to unserialize it and print it. It will be easier than using a regexp.

Upvotes: 1

Related Questions