Interpretting HTML in a query

RobColborne · Feb 13, 2004

Hi

I am using an application that stores a notes field as HTML for formatting. I would like to query the database directly using a query.

Is there any way through a SQL query to convert the notes to just the text the user has entered and do away with the HTML code.

A record in the datbase would have the following in the Notes field:

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<STYLE type=text/css> P, UL, OL, DL, DIR, MENU, PRE { margin: 0 auto;}</STYLE>

<META content="MSHTML 6.00.2800.1400" name=GENERATOR></HEAD>
<BODY leftMargin=1 topMargin=1 rightMargin=1><FONT face=Tahoma color=#ff0000 size=2>
<DIV>These are the notes added by the user</DIV></FONT></BODY></HTML>

I would like to be able to just pull back the text in between the <DIV> and </DIV> sections.

Robert Colborne

mimi2 · Feb 13, 2004

could you try this:
USE pubs
GO
SELECT substring (title_id,CHARINDEX('<DIV>', notes),CHARINDEX('</DIV>', notes)- CHARINDEX('<DIV>', notes) )
FROM titles
WHERE title_id = 'TC3218'

i took it from books on line:
-- Use the optional start_location parameter to start searching
-- for wonderful starting with the fifth character in the notes
-- column.
USE pubs
GO
SELECT CHARINDEX('wonderful', notes, 5)
FROM titles
WHERE title_id = 'TC3218'

RobColborne · Feb 13, 2004

Hi

Thanks,I was looking for somnething like this in Books Online but couldn't find it...so thanks again

Rob

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Interpretting HTML in a query

RobColborne

MIS

mimi2

Technical User

RobColborne

MIS

Similar threads

Part and Inventory Search

Sponsor