How about:
STRING_SPLIT[
^] and
STRING_AGG[
^]
They are MS SQL Server specific. If your database provider is different than MS SQL Server, you forgot to mention that in your question.
Usage:
CREATE TABLE testNames
(
LongName varchar(1000)
);
INSERT INTO testNames(LongName)
VALUES('Group Life@LH NA - GRL'),
('New York@PropFac NA;New York@CasFac NA;New Zealand@LHI - SACUKIAN'),
('Boston@CasFac NA;New York@CasFac NA'),
('East/West@LHI - MED');
SELECT STRING_AGG(a.mail, ';') NewValue
FROM
(
SELECT t.*, ROW_NUMBER() OVER(ORDER BY t.LongName) id, LEFT(m.value, CHARINDEX('@', m.value)-1) mail
FROM
(
SELECT ROW_NUMBER() OVER(ORDER BY LongName) rn, LongName
FROM testNames
) t CROSS APPLY STRING_SPLIT(LongName, ';') m
) a
GROUP BY a.rn;
SQL Server 2019 | db<>fiddle[
^]
Result:
Boston;New York
East/West
Group Life
New York;New York;New Zealand
Another way to resolve your issue is to use
Common Table Expressions[
^].