MySQL Get distinct values from custom (|) separated strings

Question

I have a table. Let's name it user_errors

user_id     errors
    1       E001|E003
    1       E005|E001|E003
    3       E009|E002|E004
    2       E001|E005|E004|E006

I have another table which has error description. Let's say error_description

err_code    error_description
  E001          Error 01
  E002          Error 02
  E003          Error 03
  E004          Error 04
  E005          Error 05
  E006          Error 06
  E007          Error 07
  E008          Error 08
  E009          Error 09

I want to achieve below result.

Get distinct error codes from user_errors table. Just the distinct errors column

errors
 E001
 E002
 E003
 E004
 E005
 E006
 E009

Answer 1

Try this query

select distinct trim(substring_index(substring_index(errors, '|', n.n), '|', -1)) as values from user_errors t cross join (select 1 as n union all select 10 ) n order by values