0

I have a field with value :
a:3:{i:0;s:3:"945";i:1;s:3:"946";i:2;s:3:"947";}

Objective is to get the below output (basically all numbers sandwiched between double quotes):
945
946
947

Regex matches have a global matching which returns an array of multiple matches, is there a way to do this in MySQL ?

I tried the below :

SELECT * 
FROM   (SELECT 'a:3:{i:0;s:3:"945";i:1;s:3:"946";i:2;s:3:"947";}' AS input) T1
WHERE  T1.input REGEXP '(")[0-9]+(")';

The query needs to work on a single row only, need not run on full table.

BHG
  • 1
  • You want 3 separate rows? If that's the case, this gets to be some REALLY nasty code: https://stackoverflow.com/questions/17942508/sql-split-values-to-multiple-rows (Have you considered postgres?) – Blue Oct 05 '18 at 08:21
  • 1
    This would be a [rather trivial task](https://www.db-fiddle.com/f/f3TVCFk3swTKfjTpe5oQBQ/0) with postgres. – Blue Oct 05 '18 at 08:31
  • yes 3 separate rows, in MySQL. I think I might have to make a stored procedure and pass the input string to it.. haven't been able to find any other solution. – BHG Oct 06 '18 at 12:56
  • Unserialize and do the task in your client. – Rick James Oct 06 '18 at 21:03

0 Answers0