As you know, in MySQL/MariaDB there is no native function for splitting string column by comma or by some regex into a table. In PostgreSQL we a great function regexp_split_to_table for this, but in mysql compatible world it was not possible (till some time ago) to do this without complicated workarounds. Fortunately with introduction of RECURSIVE CTEs support in MySQL 8 and MariaDB 10.2, there is a native a relatively easy way to do this.

Here is sample code that will parse out numbers using regex out of given string column (i.e. “8001,8002 8003, 8004”) and generate a row with incrementing port_id for each of these numbers (1 => 8001, 2 => 8002, 3 => 8003, 4 => 8004).

CREATE OR REPLACE VIEW service_port AS
  WITH RECURSIVE ha_port AS (
    SELECT
       1 AS port_id,
       -- get first match as our PORT
       regexp_substr(src.haproxy_ports, '[[:digit:]]+') AS client_port,
       -- generate remainder for recursive subqueries
       substr(src.haproxy_ports, regexp_instr(src.haproxy_ports, '[[:digit:]]+', 1, 2)) AS client_ports
    FROM service ms
    WHERE regexp_substr(src.haproxy_ports, '[[:digit:]]+') IS NOT NULL
        UNION ALL
    SELECT
       port_id + 1 AS port_id,
       regexp_substr(haproxy_ports, '[[:digit:]]+') AS client_port,
       substr(haproxy_ports, regexp_instr(haproxy_ports, '[[:digit:]]+', 1, 2)) AS haproxy_ports
    FROM ha_port p
    WHERE length(haproxy_ports) > 0
  )
  SELECT DISTINCT -- XXX: DISTINCT here, is CTE optimization barrier, because mysql 8 dies without it, while merging tables on join
     *
   FROM ha_port;

Note that, this is a not tested sample query, it should only give you an idea how it works. I could not put the exact query I’m using here, because its a bit more complicated…

If you need help with this, or similar SQL related problems, don’t hesitate to contact me.