How to save HTML data in Sql Server

Question

I have feedback panel where user can write HTML formated feedback using AJAX HTMLEditor

I want to save this HTML DATA in SQL server

HTML SOURCE

This is <span style="font-weight: bold; ">nice</span> question

HTML OUTPUT

This is nice question

Now how can i search to my database if your find "is nice" then my query can not response is nice because database contains HTML tags too.

So what are best practices to save and retrieve HTML data using SQL Query & ASP.net.

Answer 1

You might get some mileage out the SQL Server's full-text search capability. Here is a resource that describes strategies to apply full-text search to HTML text stored in SQL Server:

http://www.developmentnow.com/blog/SQL+Server+2005+Full+Text+Search+On+HTML+Documents.aspx

Answer 2

If you're using SQL Server 2008 then full-text indexing is a good option. Store your HTML in a varbinary(max) column and set its associated file type to ".html" in a file type column. The full-text indexer will parse the data as HTML and search only the text content while ignoring the HTML tags.

Answer 3

Store the data twice in two different columns; once as HTML and another as just plain text. Do the display from the HTML column, and perform any searches against the text column.

Answer 4

Another answer is to use a CTE to strip out the HTML before doing a search.

The following CTE extracts likely rows that satisfy the search criteria and recursively strips out the HTML. The Query then uses the results of the CTE to filter out rows still containing HTML and ones that don’t exactly match the search criteria.

The CTE isn’t as complicated as it looks. Most of the fiddling is to cope with PATINDEX returning 0.

--** Test table
DECLARE @HTML TABLE (id INT IDENTITY, html VARCHAR(max))
INSERT INTO @HTML SELECT 'This is a <span style="font-weight: bold; ">nice</span> question';
INSERT INTO @HTML SELECT 'The cat sat <span style="font-weight: bold; ">on the</span> mat';

--** Search criteria
DECLARE @Search VARCHAR(50) = 'is a nice';

--** CTE to return the matching rows ignoring the HTML
;WITH Search_CTE (html_id, html_text)
AS (
    SELECT h.id AS 'html_id'
         , LEFT(h.html,REPLACE(PATINDEX('%<%',h.html)-1,-1,999999)) + SUBSTRING(h.html,CONVERT(INT,REPLACE(PATINDEX('%>%',h.html)+1,1,999999)),LEN(h.html)) AS 'html_text'
      FROM @HTML AS h
     WHERE h.html LIKE '%' + REPLACE(@Search,' ','%') + '%'
     UNION ALL
    SELECT c.html_id AS 'html_id'
         , LEFT(c.html_text,REPLACE(PATINDEX('%<%',c.html_text)-1,-1,999999)) + SUBSTRING(c.html_text,CONVERT(INT,REPLACE(PATINDEX('%>%',c.html_text)+1,1,999999)),LEN(c.html_text)) AS 'html_text'
      FROM Search_CTE AS c
     WHERE PATINDEX('%<%',c.html_text) > 0
)
SELECT h.html AS 'Original HTML'
     , cte.html_text AS 'HTML Text'
  FROM Search_CTE AS cte
  JOIN @HTML AS h
    ON h.id = cte.html_id
 WHERE PATINDEX('%<%',cte.html_text) = 0   --** Filter out rows still containing HTML
   AND html_text LIKE '%' + @Search + '%'; --** Filter out rows not matching the search criteria

This query has the limitation that it doesn't handle the situation where > or < is in the text, but this can be coded around if required.