CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2008-r2
Stephen Morrell
Stephen Morrell

Reputation: 33

Removing characters between brackets

I'm using SQL Server 2008 R2, but I'm accessing that server via SQL Server 2012 Management Studio.

I have a table called Search with a column Body of type (nvarchar(Max), not null).

The data in this column is actually the body text of e-mails, and this column is keeping all HTML tags as part of the text.

For example the email body would show:

 Good morning
 This invoice has been assigned.

But when it's stored in the column called Body, it looks like this:

[size=14.0pt]Good morning
[/size][size=14.0pt]This invoice has been assigned.[/size]

I've having the following SQL to remove everything between "[" and "]" (including the brackets):

 SELECT 
     *,
     CASE 
        WHEN CHARINDEX('[', Body) > 0 AND CHARINDEX(']', Body) > 0 
             AND CHARINDEX('[', Body) < CHARINDEX(']', Body) 
           THEN STUFF(Body, CHARINDEX('[', Body), (CHARINDEX(']', Body) - CHARINDEX('[', Body)) + 1, '')
           ELSE Body
     END AS Body1
FROM search

But all this does is removes the first set of Tags which now looks like this:

Good morning
[/size][size=14.0pt]This invoice has been assigned.[/size]

Can you help me with my SQL so that it will remove them all, so it will look like this:

Good Morning
This invoice has been assigned.

Upvotes: 1

Views: 506

Answers (1)

Andrea
Andrea

Reputation: 12405

You can define a scalar function that removes square brackets and all the text contained between them:

create function [dbo].[fx_removetags] (@text nvarchar(max))
    returns nvarchar(max) as
begin
    declare @tag_start  int
    declare @tag_end    int
    declare @tag_length int
    set @tag_start = charindex('[', @text)
    set @tag_end = charindex(']', @text, charindex('[', @text))
    set @tag_length = (@tag_end - @tag_start) + 1
    while @tag_start > 0 and @tag_end > 0 and @tag_length > 0
        begin
            set @text = stuff(@text,@tag_start,@tag_length, '')
            set @tag_start = charindex('[',@text)
            set @tag_end = charindex(']',@text,charindex('[', @text))
            set @tag_length = (@tag_end - @tag_start) + 1
        end
    return ltrim(rtrim(@text))
end

Now you can invoke the function to get the text without brackets:

declare @test nvarchar(max)= ''
set @test = '[size=14.0pt]Good morning [/size][size=14.0pt]This invoice has been assigned.[/size] '

select [dbo].[fx_RemoveTags](@test)

Result:

enter image description here

In your specific case you use the function like this:

SELECT 
     *,
     [dbo].[fx_RemoveTags](@test) as Body1
FROM search

Upvotes: 2

Related Questions