Click here to Skip to main content
15,867,777 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 2: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[^].
 
Share this answer
 

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