Extracting values out of serialized php array in postgresql

Question

Problem:

one column of a table contains serialized php arrays. i'd like to extract values of that serialized data structure without using php. i'd need a postgres sql statement to get those values.

Example:

here's the part of the serialized datastructure, i'd need (the bold part):

... s:12:"SearchtermID";s:4:"1008"; ....

THANKS!

for json this will be great, for a php serialized string not so great :) — kertal
– kertal, Commented Oct 6, 2011 at 20:39

Erwin Brandstetter · Accepted Answer · 2011-10-06 16:06:07Z

2

This will work in your example:

SELECT substring('... s:12:"SearchtermID";s:4:"1008"; ....', 's:4:"([0-9]+)"');

See the manual here and here.
You may want to provide more details ...

answered Oct 6, 2011 at 16:06

Erwin Brandstetter

669k160 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

kertal Over a year ago

thanks :) this is the right trail, for my case this adaption works now. needed the name of the serialized field to filter out the right value. select substring(db-fieldname from E'"php-fieldname";s:\\d+:"(\\d+)"') from db-tablename

Nikls Niklson · Accepted Answer · 2022-10-27 20:36:43Z

0

This my solution:

select substring((regexp_matches(db_fieldname,'("\d+")','g'))[1] from '"(\d+)"') from db_tablename

answered Oct 27, 2022 at 20:36

Nikls Niklson

1

Collectives™ on Stack Overflow

Extracting values out of serialized php array in postgresql

Problem:

Example:

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Problem:

Example:

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related