Extracting part of string and loading

Question

hello folks,

i have a column i trying to load into destination but not sure how to do it in ssis?

the data is like this below

column1 :

<div class="ExternalClassDB075A28F1AB44DA882ED25EFAA3FD94">Take trial testimony & attempt to settle up to 175k</div>

just want to only extract this part - 'Take trial testimony & attempt to settle up to 175k' and load it to destination.

Answer 1

You can create a view to retrieve the text from the tag <div>...</div>, and then use SSIS to load that column to the destination table.

DECLARE @c varchar(1000) = '<div class="ExternalClassDB075A28F1AB44DA882ED25EFAA3FD94">Take trial testimony &amp; attempt to settle up to 175k</div>';
SELECT SUBSTRING(@c, CHARINDEX('>', @c) + 1, CHARINDEX('</div>', @c) - CHARINDEX('>', @c) - 1);

Answer 2

Hi @Remo522 ,

What data type is your source column?
Is it XML by any chance?

Check it out.

T-SQL

-- DDL and sample data population, start  
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, col NVARCHAR(MAX));  
INSERT INTO @tbl (col) VALUES  
(N'<div class="ExternalClassDB075A28F1AB44DA882ED25EFAA3FD94">Take trial testimony &amp; attempt to settle up to 175k</div>'),  
(N'<div class="ExternalClass5D1ECD3D7E8E4613B043854E073CD5E9">  
	<span id="ms-rterangepaste-start">  
	</span>Continuance is being filed as we have a May 4th trial setting. Authority to settle Joshua Francis up to 240,000<span id="ms-rterangecursor-start">  
	</span><span id="ms-rterangecursor-end"></span></div>');  
-- DDL and sample data population, end  
  
;WITH rs AS  
(  
	SELECT *   
		, colXML = TRY_CAST(col AS XML)  
	FROM @tbl  
)  
SELECT rs.ID  
	, c.value('(./text())[1]','VARCHAR(100)') AS colText  
FROM rs CROSS APPLY colXML.nodes('/div') AS t(c);  

Output

ID colText  
1 Take trial testimony & attempt to settle up to 175k  
2 Continuance is being filed as we have a May 4th trial setting. Authority to settle Joshua Francis up  

Answer 3

Hi @Remo522 ,

Please use TOKEN(@[User::Variable],"><",2) in Derived Column Transformation to get your desired part of data.

I have tested successfully in Derived Column Transformation.

I have also tested successfully in the Expression Builder of the variable.

Please refer to the following link and pictures:
TOKEN (SSIS Expression)

Best Regards,
Mona

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.