Is there a way to split text from cell into multiple records in SQL using line break delimiter?

I have a system generated report that exports all expense reports for a given period. All employees attached to the report are exported in one field “Attendees”, separating multiple employees with line breaks.

I would like to break the cell into multiple records, still including the expense report NO. on each record.

Note, the names in the attendee field are different formats, but they all end with a “)”.

Is there a way to use the line break as a delimiter and create a new record with each break, and populate with the same values of the other fields in the original record?

SQL would be the ideal interface, but I can use Python, R, Tableau, and PowerBI before importing into the server if need be.

Example below.

&

Answer

SQL Server:

SELECT ExpRep, ltrim(value+')')
FROM employees
    CROSS APPLY STRING_SPLIT(Attendees, ')')
WHERE RTRIM(value) <> ''; 

Oracle:

select ExpRep
       , Attendees || ')'
from (select ExpRep
             , ltrim(regexp_substr(Attendees, '[^)]+', 1, levels.column_value)) as Attendees
      from test
           , table(cast(multiset(select level 
                                 from dual connect by  level <= length (regexp_replace(Attendees, '[^)]+'))  + 1) as sys.OdciNumberList)) levels)
where Attendees is not null;

MySQL: (Only valid for examples where you will have 5 different employees in column Attendees. If yes then add union all…)

select
  ExpRep,
  concat(ltrim(SUBSTRING_INDEX(SUBSTRING_INDEX(Attendees, ')', numbers.n), ')', -1)),')') name
from (select 1 n 
      union all select 2 
      union all select 3 
      union all select 4 
      union all select 5) numbers INNER JOIN test
  on CHAR_LENGTH(Attendees)
     -CHAR_LENGTH(REPLACE(Attendees, ')', ''))>=numbers.n-1
where ltrim(SUBSTRING_INDEX(SUBSTRING_INDEX(Attendees, ')', numbers.n), ')', -1)) <> ''     
order by
  ExpRep, n

Note It would be nice that one of this will be of use to you but next time do add a tag of a database you use.