CODEWITHSUNDEEP
sql-serverssis
DenStudent
DenStudent

Reputation: 928

SSIS - remove character when it has alphabetic character infront en behind it

Let's say I have data imported from a csv in SSIS that looks like this:

ID,Name,Year,Place
1,"John",1994,"UK"
2,"Bill",1899,"USA"
3,"Gill",1989,"Germ"any"

In this case, there is a typo in Germany

I was wondering if there was a way (in derived column component maybe) to remove the " When the text is like _"_

_ being an alphabetic character or space.

This question came close to what I needed: SSIS - remove character X unless it's followed by character Y , but it's done in SQL, while my data is in SSIS from a csv.

Upvotes: 3

Views: 154

Answers (2)

userfl89
userfl89

Reputation: 4810

The answer that @Sami posted is a good option, and if you are specifically aiming to use a derived column for this then the following expression will remove any " while still keeping the leading and trailing double quotes. Make sure to escape the " with a \ character, otherwise the SSIS expression will fail.

"\"" + REPLACE(Place,"\"","") + "\""

Upvotes: 1

Ilyes
Ilyes

Reputation: 14928

You can do like

CREATE TABLE T
    ([ID] int, [Name] varchar(45), [Year] int, [Place] varchar(45));

INSERT INTO T
    ([ID], [Name], [Year], [Place])
VALUES
    (1, '"John"', 1994, '"UK"'),
    (2, '"Bill"', 1899, '"USA"'),
    (3, '"Gill"', 1989, '"Germ"an"y"');

SELECT ID,
       Name,
       [Year],
       CONCAT('"', REPLACE(Place, '"', ''), '"') Place
FROM T;

Returns:

+----+--------+------+-----------+
| ID |  Name  | Year |   Place   |
+----+--------+------+-----------+
|  1 | "John" | 1994 | "UK"      |
|  2 | "Bill" | 1899 | "USA"     |
|  3 | "Gill" | 1989 | "Germany" |
+----+--------+------+-----------+

Demo

Upvotes: 1

Related Questions