Click here to Skip to main content
15,121,818 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I want to remove the multiple characters starting from '@' till the ';' in SSIS.
For example,

my input is
New York@PropFac NA;New York@CasFac NA;New Zealand@LHI - SACUKIAN
New York@CasFac NA
Stamford@PropFac NA
East@P&C Cologne Intl
Boston@CasFac NA;New York@CasFac NA


and want the output as,
New York;New York;New Zealand
New York
Stamford
East
Boston;New York


Note: Length after '@' is not fixed.

What I have tried:

SQL
create table dbo.test 
(
Name varchar(1000)
)

insert into dbo.test
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')
Posted
Updated 12-Jan-21 3:13am
v2

1 solution

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:
SQL
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[^].
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900