Remove data from column with HTML tag or special symbol

Question

Hi Experts,

In the db I have a column with html phrase inside <Just want to Know> bracket. my requriment is ignore the html tag and bring the data.

• Sample

PQWS,<Get>was it,<Mas>
Which one, </u>, is like ,<new one> ,you got

• Expected Result

PQWS, was it
Which one is like you got

I want the result in ssis and t-sql. Please help

Answer 1

Try one of solutions:

;
with Q as
(
    select mycolumn, charindex('<', mycolumn) as a, charindex('>', mycolumn) as b
    from MyTable
    union all
    select s, charindex('<', s) as a, charindex('>', s) as b
    from Q
    cross apply (values (stuff(mycolumn, a, b - a + 1, '' ))) t(s)
    where b > a
)
select * 
from Q
where a = 0
option (maxrecursion 0)

Answer 2

Hi @San ，

From SSIS Derived Column expression side, it's hard to meet your need.

Maybe you could explore the Script Task, writing a short VB or C# script that takes a line of raw text (with HTML tags) as input, cleans it up and

Then returns clean data (one output parameter per field). Both VB and C# are more flexible at the string manipulation level than SSIS.

A similar example for your reference:

http://microsoft-ssis.blogspot.com/2011/03/cleaning-with-regular-expressions-in.html

Regards，

Zoe

---

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.
Hot issues October

Answer 3

Hi @San ，

Please also check:

DECLARE @string varchar(max)  
DECLARE @num int  
DECLARE @result varchar(max)  
  
SET @num=1  
SET @result='PQWS,<Get>was it,<Mas>'  
WHILE @num>0  
  
BEGIN   
 SET @result=REPLACE(@result,SUBSTRING(@result,CHARINDEX('<',@result)  
,CHARINDEX('>',@result)-CHARINDEX('<',@result)+1),'')  
  
 SET @num=CHARINDEX('<',@result)  
  
 END  
  
SELECT @result  

If you have any question, please feel free to let me know.
If the response is helpful, please click "Accept Answer" and upvote it.

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.