SQL MariaDB apply regex to column

I'm moving a couple operations from my backend services to the database. I'm currently creating a view to transform a couple tables into a single table. As an example, the user table has one row per user, and the user meta table has one row per meta field per user, so each user can have multiple meta fields, hence why I'd like to combine this into a single table through a view.

Sidenote, DBUser has been redacted.

create definer = DBUser@`%` view mm_users as
select `DBUser`.`wp_users`.`ID`                                                                             AS `id`,
       `DBUser`.`wp_users`.`user_login`                                                                     AS `login`,
       `DBUser`.`wp_users`.`user_email`                                                                     AS `email`,
       max(if(`DBUser`.`wp_usermeta`.`meta_key` = 'nickname',
              `DBUser`.`wp_usermeta`.`meta_value`,
              NULL))                                                                                        AS `nickname`,
       max(if(`DBUser`.`wp_usermeta`.`meta_key` = 'first_name',
              `DBUser`.`wp_usermeta`.`meta_value`,
              NULL))                                                                                        AS `first_name`,
       max(if(`DBUser`.`wp_usermeta`.`meta_key` = 'last_name',
              `DBUser`.`wp_usermeta`.`meta_value`,
              NULL))                                                                                        AS `last_name`,
       max(if(`DBUser`.`wp_usermeta`.`meta_key` = 'user-jahrgangsstufe',
              `DBUser`.`wp_usermeta`.`meta_value`, NULL))                                                   AS `class`,
       max(case
               when `DBUser`.`wp_usermeta`.`meta_key` = 'user-schule'
                   then `DBUser`.`wp_usermeta`.`meta_value` end)                                            AS `school`,
       case
           when `DBUser`.`wp_usermeta`.`meta_key` = 'je_data_store_angebote-favoriten'
               then regexp_replace(`DBUser`.`wp_usermeta`.`meta_value`, 's:4:"([0-9]{1,4})"',
                                   '\\1') end                                                               AS `favorites`,
       max(case
               when `DBUser`.`wp_usermeta`.`meta_key` = 'wp_capabilities' then regexp_replace(
                       `DBUser`.`wp_usermeta`.`meta_value`,
                       '^a:1:{s:[0-9]+:"(subscriber|editor|contributor|administrator)";b:1;}$', '\\1') end) AS `role`
from (`DBUser`.`wp_users` left join `DBUser`.`wp_usermeta`
      on (`DBUser`.`wp_users`.`ID` = `DBUser`.`wp_usermeta`.`user_id`))
where `DBUser`.`wp_usermeta`.`meta_key` in
      ('nickname', 'first_name', 'last_name', 'wp_capabilities', 'user-jahrgangsstufe', 'user-schule',
       'je_data_store_angebote-favoriten')
group by `DBUser`.`wp_users`.`ID`;

Everything works apart from transforming the je_data_store_angebote-favoriten metadata. For clarification, this meta value is a serialized PHP string with 0 to 5 values, a couple examples:

a:0:{}
a:1:{i:0;s:4:"3131";}
a:2:{i:0;s:4:"2535";i:1;s:4:"2539";}
a:3:{i:0;s:4:"3119";i:1;s:4:"3086";i:2;s:4:"2615";}
a:4:{i:0;s:4:"2950";i:1;s:4:"3185";i:2;s:4:"2478";i:3;s:4:"2568";}
a:5:{i:0;s:4:"3025";i:1;s:4:"3032";i:2;s:4:"3202";i:3;s:4:"2550";i:4;s:4:"3080";}
a:5:{i:0;s:4:"25";i:1;s:4:"7";i:2;s:4:"33556";i:3;s:4:"4597";i:4;s:4:"21594";}

I've put s:4:"([0-9]{1,4})" into <https://regex101.com/> and it matches everything I need it to, but when creating this view, the favourites column is null for all users. What gives? I can apply a regex pattern to get the role of the user, why can't I do the same to get the favourites of a user?

That was a tricky one, but as i thought you can use GROUP_CONCAT

to get rid of the empty one, you can do as you already have done

CREATE TABLE tex
(`val` varchar(81))
;
INSERT INTO tex
(`val`)
VALUES
('a:0:{}'),
('a:1:{i:0;s:4:"3131";}'),
('a:2:{i:0;s:4:"2535";i:1;s:4:"2539";}'),
('a:3:{i:0;s:4:"3119";i:1;s:4:"3086";i:2;s:4:"2615";}'),
('a:4:{i:0;s:4:"2950";i:1;s:4:"3185";i:2;s:4:"2478";i:3;s:4:"2568";}'),
('a:5:{i:0;s:4:"3025";i:1;s:4:"3032";i:2;s:4:"3202";i:3;s:4:"2550";i:4;s:4:"3080";}'),
('a:5:{i:0;s:4:"25";i:1;s:4:"7";i:2;s:4:"33556";i:3;s:4:"4597";i:4;s:4:"21594";}')
;

> status
> Records: 7 Duplicates: 0 Warnings: 0
>

SELECT
GROUP_CONCAT(SUBSTR(REGEXP_REPLACE(val, '^a:\\d+:\\{\\\}|a:\\d+:\\{i:\\d+;s:\\d+:"|";i:\\d+;s:\\d+:"|";\\}$', ','),2) SEPARATOR  "") AS string_values
FROM
tex;

fiddle