We help IT Professionals succeed at work.
Get Started

need SQL code to extract hyperlink URL and clickable text from nvarchar field populated with html

bbraman
bbraman asked
on
1,251 Views
Last Modified: 2013-11-05
I have a table of product descriptions (details) where most every record includes some html formatting and one or more hyperlinks.  I need to extract from these product descriptions a list of all the hyperlink URLs contained in all of the product description records in my table, and as a separate result set column, the clickable text associated with each hyperlink URL.

A good answer to this questions will be a SQL statement executable against SQL Server 2005 that generates a list of URLs of <a href> style hyperlinks contained in all the records of the product descriptions (details), and as a separate field in the result, the clickable text that activates each URL.  Note that some records in my table contain no html formatting and some records will contain one or more hyperlinks whose URL may be enclosed in single, <a href=' '>, or double <a href=" ">, quotes.

example table:
create table productTable
(
  details nvarchar(2650)
)
go
INSERT productTable VALUES ('<b>test product 1</b><br><i>example text</i>test<a href="http://www.domain.com/pc/Search?keyWord=test&type=1&">click here</a>example text: example text<h2>example text</h2>') 
INSERT productTable VALUES ('<b>test product 2</b><br><i>example text</i>test<a href=''/cp/pc/product?product=2''>click here</a>example text: example text<h2>example text</h2>')
INSERT productTable VALUES ('test product 3 (example text)<br><br><i>example text</i>test<br><a href='' domain.com/cp/pc/product?product=3''>click here</a>example text: example text<br><a href="product?product=2010">temp</a><h2>example text</h2>') 

INSERT productTable VALUES ('<b>test product 4 (example text)</b><br><br><i>example text</i>test<br>example text: example text<br><h2>example text</h2>')
INSERT productTable VALUES ('test product 4 (example text)<br><br><i>example text</i>test<br><a href=''/cp/product?product=2010''>temp</a>example text: example text<br><a href="domain.com/cp/pc/product?product=3">click here</a><h2>example text</h2>') 
 
go

Open in new window


result set:
cp/product?product=2010                                                      example text
/cp/pc/product?product=2                                                     click here
domain.com/cp/pc/product?product=3                                           click here
http://www.domain.com/pc/Search?keyWord=test&type=1&                         click here
product?product=2010                                                         temp

Open in new window


Thank You for considering my question.
Comment
Watch Question
Commented:
This problem has been solved!
Unlock 1 Answer and 17 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE