How to remove multiple characters between 2 special characters in a column in SSIS expression

Question

I want to remove the multiple characters starting from '@' till the ';' in derived column expression in SSIS. For example,

my input column values are,

and want the output as,

Note: Length after '@' is not fixed.

Already tried in SQL but want to do it via SSIS derived column expression.

Answer 1

First of all: Please do not post pictures. We prefer copy-and-pastable sample data. And please try to provide a minimal, complete and reproducible example, best served as DDL, INSERT and code as I do it here for you.

And just to mention this: If you control the input, you should not mix information within one string... If this is needed, try to use a "real" text container like XML or JSON.

SQL-Server is not meant for string manipulation. There is no RegEx or repeated/nested pattern matching. So we would have to use a recursive / procedural / looping approach. But - if performance is not so important - you might use a XML hack.

--DDL and INSERT

DECLARE @tbl TABLE(ID INT IDENTITY,YourString VARCHAR(1000));
INSERT INTO @tbl VALUES('Here is one without')
                      ,('One@some comment;in here') 
                      ,('Two comments@some comment;in here@here is the second;and some more text') 

--The query

SELECT t.ID
      ,t.YourString
      ,CAST(REPLACE(REPLACE((SELECT t.YourString AS [*] FOR XML PATH('')),'@','<!--'),';','--> ') AS XML) SeeTheIntermediateXML
      ,CAST(REPLACE(REPLACE((SELECT t.YourString AS [*] FOR XML PATH('')),'@','<!--'),';','--> ') AS XML).value('.','nvarchar(max)') CleanedValue
FROM @tbl t

The result

+----+-------------------------------------------------------------------------+-----------------------------------------+
| ID | YourString                                                              | CleanedValue                            |
+----+-------------------------------------------------------------------------+-----------------------------------------+
| 1  | Here is one without                                                     | Here is one without                     |
+----+-------------------------------------------------------------------------+-----------------------------------------+
| 2  | One@some comment;in here                                                | One in here                             |
+----+-------------------------------------------------------------------------+-----------------------------------------+
| 3  | Two comments@some comment;in here@here is the second;and some more text | Two comments in here and some more text |
+----+-------------------------------------------------------------------------+-----------------------------------------+

The idea in short:

• Using some string methods we can wrap your unwanted text in XML comments.

Look at this

Two comments<!--some comment--> in here<!--here is the second--> and some more text

• Reading this XML with .value() the content will be returned without the comments.

Hint 1: Use '-->;' in your replacement to keep the semi-colon as delimiter.

Hint 2: If there might be a semi-colon ; somewhere else in your string, you would see the --> in the result. In this case you'd need a third REPLACE() against the resulting string.