MySQL mapping within a list

Question

i've got some country code values in a coulmn comma separated, how do i return their corresponding mapped values (e.g. ccy in my sample below)?

| country    | ccy           |
+------------+---------------+
| HK         | HKD           |
| JP         | JPY           |
| JP, KR     | JPY, KRW      |
| AU, NZ     | AUD, NZD      |
| US, UK, EU | USD, GBP, EUR |

the individual mapping is stored in a separate table data_mapping:

| country | ccy |
+---------+-----+
| HK      | HKD |
| JP      | JPY |
| KR      | KRW |
| AU      | AUD |
| NZ      | NZD |
| US      | USD |
| UK      | GBP |
| EU      | EUR |

basically i would need a function f_ccy(country_list) with ccy_list returned, which will split, lookup & merge back into a list

is it feasible?

while the "this isn't how you should design your database" answer is quite correct, this is feasible as is. see https://dbfiddle.uk/?rdbms=mariadb_10.5&fiddle=66445224e1df3b16bcf4f54d6a63a956 — ysth, Dec 02 '20 at 22:05

Tim Biegeleisen · Accepted Answer · 2020-12-02T07:30:27.730

I'm just going to answer directly here by saying that you absolutely should move away from storing CSV in your SQL tables. Use this version instead:

| country | grp | pos |
+---------+-----+-----+
| HK      | 1   | 1   |
| JP      | 2   | 1   |
| JP      | 3   | 1   |
| KR      | 3   | 2   |
| AU      | 4   | 1   |
| NZ      | 4   | 2   |
| US      | 5   | 1   |
| UK      | 5   | 2   |
| EU      | 5   | 3   |

Now with this proper table design in hand, you only need a basic join with aggregation:

SELECT
    t.grp,
    GROUP_CONCAT(t.country ORDER BY t.pos SEPARATOR ', ') AS country,
    GROUP_CONCAT(m.ccy ORDER BY t.pos SEPARATOR ', ') AS ccy
FROM yourTable t
INNER JOIN mapping m
    ON m.country = t.country
GROUP BY
    t.grp;

MySQL mapping within a list

1 Answers1

Demo