StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Heres a function I wrote to remove HTML tags (pairs of <...>) using a set based approached. I am interested to see if you can repurpose to use to strip Word meta tags.</p> <pre><code>----------------------------------------------------------- -- 1. create a number table (this is just a utility table) ----------------------------------------------------------- set nocount on; if object_id('dbo.Number') is not null begin drop table dbo.Number; end go create table dbo.Number (n int identity(1,1) primary key); insert dbo.Number default values ; while scope_identity() < 500 insert dbo.Number default values ; ----------------------------------------------------------- -- 2. create the function (leverages the utility table) ----------------------------------------------------------- if object_id('dbo.ufn_StripHTML') is not null begin drop function dbo.ufn_StripHTML; end go create function dbo.ufn_StripHTML ( @Input varchar(8000), @Delimiter char(1) ) returns varchar(8000) as begin declare @Output varchar(8000) select @Input = replace(replace(@input, '<', @Delimiter), '>', @Delimiter) select @Output = isnull(@Output, '') + s from ( select row_number() over (order by n.n asc) [i], substring(@Delimiter + @Input + @Delimiter, n.n + 1, charindex(@Delimiter, @Delimiter + @Input + @Delimiter, n.n + 1) - n.n - 1) [s] from dbo.Number n where n.n = charindex(@Delimiter, @Delimiter + @Input + @Delimiter, n.n) and n.n <= len(@Delimiter + @Input) ) d where i % 2 = 1 return @Output end go ----------------------------------------------------------- --3. Example of calling the function when you query ----------------------------------------------------------- if object_id('tempdb..TEMP_PN') is not null drop table #TEMP_PN; create table #TEMP_PN (note_text varchar(max), event_log_id int); insert into #TEMP_PN select '<b>Some very large bolded text here!</b>', 1 union all select 'no tags here', 2 union all select '<html><body><h1>My First Heading</h1><p>My first paragraph.</p></body></html>', 3 select [Strip] = dbo.ufn_StripHTML(note_text, '|'), [Orig] = note_text, event_log_id from #TEMP_PN </code></pre> <p>Edit: ported scalar to table</p> <pre><code>alter function dbo.ufn_StripHTMLTable ( @Input varchar(8000), @Delimiter char(1) ) returns @ret table (OutString varchar(8000)) as begin declare @Output varchar(8000) select @Input = replace(replace(@input, '<', @Delimiter), '>', @Delimiter) select @Output = isnull(@Output, '') + s from ( select row_number() over (order by n.n asc) [i], substring(@Delimiter + @Input + @Delimiter, n.n + 1, charindex(@Delimiter, @Delimiter + @Input + @Delimiter, n.n + 1) - n.n - 1) [s] from dbo.Number n where n.n = charindex(@Delimiter, @Delimiter + @Input + @Delimiter, n.n) and n.n <= len(@Delimiter + @Input) ) d where i % 2 = 1; insert into @ret values(@Output); return; end </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload