clear markdown feedback
clear markdown feedback
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 3364508 fiddles created (40549 in the last week).

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');
4 rows affected
 hidden batch(es)


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;
NewValue
Boston;New York
East/West
Group Life
New York;New York;New Zealand
 hidden batch(es)