SQL Server query to delete text from text column

Question

I have a SQL Server database with a table feedback that contains a text column comment. In that column I have tag data, for example

This is my record <tag>Random characters are here</tag> with information. 

How do I write a query to update all of these records to remove the <tag></tag> and all of the text in between?

I'd like to write this to a different 'temporary' table to first verify the changes and then update the original table.

I am running SQL Server 2014 Express.

Thank you

Answer 1

Here is a function to remove tags..

CREATE FUNCTION [dbo].[RemoveTag](@text NVARCHAR(MAX), @tag as nvarchar(max))
RETURNS NVARCHAR(MAX)
AS
BEGIN
    declare @startTagIndex as int
    declare @endTagIndex as int

    set @startTagIndex = CHARINDEX('<' + @tag + '>', @text)
    if(@startTagIndex > 0) BEGIN
        set @endTagIndex = CHARINDEX('</' + @tag + '>', @text, @startTagIndex)
        if(@endTagIndex > 0) BEGIN
            return LEFT(@text, @startTagIndex - 1) + RIGHT(@text, len(@text) - len(@tag) - @endTagIndex  - 2)
        END
    END

    return @text
END

Later you can use it like:

Update table set field = dbo.RemoveTag(field, 'tag')

If you want to write fields to other table then:

CREATE TABLE dbo.OtherTable (
    OtherField nvarchar(MAX) NOT NULL
) 
GO

INSERT INTO OtherTable (OtherField)
SELECT dbo.RemoveTag(field, 'tag') from table