Extracting Text From HTML With SQL

Question

I have HTML in my database table which is of following Format

 <img style='border-style: none' src='../Images/flag_Green.gif' onmouseover='ddrivetip("<table     border=1 cellpadding=1 width=100%><tr><th nowrap width=20%>My Status</th><th>My Details</th></tr><tr><td>Green</td><td>Compliant - 06-0907370</td></tr></table>", 400, null, this);' onmouseout='hideddrivetip(this)'></img>

I need to extract colour value from this e.g. in this Case it's "green" Can anyone help me to write a function that can extract this? It's between first td tag

Try using the power of PATINDEX and SUBSTRING. If your code doesn't work, post it here for assistance. — Tab Alleman
– Tab Alleman, Commented Aug 27, 2014 at 14:28
Because data is in SQL Server and i want to select value between td along with other columns — InTheWorldOfCodingApplications
– InTheWorldOfCodingApplications, Commented Aug 27, 2014 at 14:56

Sean Lange · Accepted Answer · 2014-08-27 15:04:18Z

1

Pretty simple using SUBSTRING and PATINDEX as suggested by Tab Alleman. Here is one way you could do this.

declare @String varchar(1000) = '<img style=''border-style: none'' src=''../Images/flag_Green.gif'' onmouseover=''ddrivetip("<table     border=1 cellpadding=1 width=100%><tr><th nowrap width=20%>My Status</th><th>My Details</th></tr><tr><td>Green</td><td>Compliant - 06-0907370</td></tr></table>", 400, null, this);'' onmouseout=''hideddrivetip(this)''></img>'

select SUBSTRING(@String, patindex('%<td>%', @String) + 4, patindex('%</td>%', @String) - patindex('%<td>%', @String) - 4)

answered Aug 27, 2014 at 15:04

Sean Lange

33.7k3 gold badges28 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

InTheWorldOfCodingApplications Over a year ago

I have got Green, Red and Amber. It's throwing following exception Invalid length parameter passed to the LEFT or SUBSTRING function.

Sean Lange Over a year ago

So play with it a little bit. I can't see your screen or know what your data is like. Those errors suggest that all of your data does not have the substring <td> in them.

Irfan Shaikh · Accepted Answer · 2022-07-20 06:29:56Z

I created function to extract text from html.

Create function  [dbo].[RetriveTextFromHTML](@htmlstring varchar(Max))

returns varchar(Max)

AS

BEGIN

Set @htmlstring=Replace(@htmlstring,'&nbsp;',' ');
Set @htmlstring=Replace(@htmlstring,'Note:','');
DECLARE @startTag varchar(25) = '%[<]%'
DECLARE @endTag varchar(25) = '%[>]%'
Declare @endTagIndex int =0;
Declare @startTagIndex int =0;
WHILE PATINDEX(@startTag,@htmlstring)>0
 Begin
        Set @startTagIndex=PATINDEX(@startTag,@htmlstring);
        Set @endTagIndex=PATINDEX(@endTag,@htmlstring);
        SET @htmlstring = Stuff(@htmlstring,@startTagIndex,(@endTagIndex-@startTagIndex)+1,'');
 End

return RTRIM(@htmlstring)

END

Call this method as

Select [dbo].[RetriveTextFromHTML]('<strong>Hello World</strong>')

Output:

Hello World

Collectives™ on Stack Overflow

Extracting Text From HTML With SQL

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related