Excluding HTML tags from query

Introduction

Sometimes we are inserting text with HTML-tag into a SQL Server database. But what to do if we need to execute a SQL query with Like clause on text only (without HTML tags)? Here we have to exclude HTML tags from the text then we can execute Like clause query on it. So here I'm going to show a SQL Scalar function for the same.

One user has the same requirement here: http://www.codeproject.com/Questions/536178/Excluding-html-tags-from-query and it is solved by this code snippet.

Using the code   

If we have added "Test Testimonial" it stores in database as '<p>Test Testimonial</p>" and while showing in grid it shows "Test Testimonial". If I want to run a query like "select Testimonial from Testimonials where Testimonial like 'T%' " it shows no result.

The function is here:

CREATE FUNCTION RemoveHtmlString
(
    -- Add the parameters for the function here
    @HTMLText NVARCHAR(MAX)
)
RETURNS NVARCHAR(MAX) 
AS
BEGIN
DECLARE @Start INT
    DECLARE @End INT
    DECLARE @Length INT
    SET @Start = CHARINDEX('<',@HTMLText)
    SET @End = CHARINDEX('>',@HTMLText,CHARINDEX('<',@HTMLText))
    SET @Length = (@End - @Start) + 1
    WHILE @Start > 0 AND @End > 0 AND @Length > 0
    BEGIN
        SET @HTMLText = STUFF(@HTMLText,@Start,@Length,'')
        SET @Start = CHARINDEX('<',@HTMLText)
        SET @End = CHARINDEX('>',@HTMLText,CHARINDEX('<',@HTMLText))
        SET @Length = (@End - @Start) + 1
    END
    RETURN LTRIM(RTRIM(@HTMLText))
 
END
GO

Then execute your query as

select Testimonial  from Testimonials  where dbo.RemoveHtmlString(Testimonial)   like 'T%'

The function will remove HTML tags from the field before executing the like clause.

History 

Please let me know if someone has an easier way than this.