MySQL - Delimited text field to rows using the JSON_TABLE function

select

  `t`.`DELIMITED_FIELD_NAME`

  , `csv_row`.`author`

from

  SOURCE_TABLE as t

  

  -- Delimited field to rows

  join JSON_TABLE(

    TRIM(REPLACE(json_array(`t`.`DELIMITED_FIELD_NAME`), ';', '","')),

    '$[*]'

    columns (

      `DELIMITED_FIELD_NAME`

          varchar(50)

          path '$'

    )

  ) as `csv_row`

;