SQL — удалить все теги HTML в строке
В моем наборе данных у меня есть поле, в котором хранится текст, размеченный с помощью HTML. Общий формат выглядит следующим образом:
Я мог бы попытаться решить проблему, выполнив следующие действия:
REPLACE(REPLACE(Table.HtmlData, ' ', ''), '
') Однако это не является строгим правилом, так как некоторые записи нарушают стандарты W3C и не включают теги к примеру. Хуже того, могут отсутствовать закрывающие теги. Так что мне нужно будет включить REPLACE функция для каждого открывающего и закрывающего тега, который может существовать.
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( Table.HtmlData, '', ''), '', ''), '', ''), '', ''), '', ''), '', ''), '', ''), '
', '') Мне было интересно, есть ли лучший способ сделать это, чем использование нескольких вложенных REPLACE функции. К сожалению, единственными языками, которые у меня есть в этой среде, являются SQL и Visual Basic (не.NET).
7 ответов
DECLARE @x XML = ' My text.
' SELECT t.c.value('.', 'NVARCHAR(MAX)') FROM @x.nodes('*') t(c) Обновление — для строк с незамкнутыми тегами:
DECLARE @x NVARCHAR(MAX) = ' My text.
More text.
' SELECT x.value('.', 'NVARCHAR(MAX)') FROM ( SELECT x = CAST(REPLACE(REPLACE(@x, '>', '/>'), ' Если HTML правильно сформирован, тогда нет необходимости использовать замену для разбора XML.
Просто приведите или преобразуйте его в тип XML и получите значение (я).
Вот пример для вывода текста из всех тегов:
declare @htmlData nvarchar(100) = ' My text.
My other text.
'; select convert(XML,@htmlData,1).value('.', 'nvarchar(max)'); select cast(@htmlData as XML).value('.', 'nvarchar(max)'); Обратите внимание, что есть разница в выводе пробелов между приведением и преобразованием.
Чтобы получать контент только от определенного узла, используется синтаксис XQuery. (XQuery основан на синтаксисе XPath)
select cast(@htmlData as XML).value('(//body/p/node())[1]', 'nvarchar(max)'); select convert(XML,@htmlData,1).value('(//body/p/node())[1]', 'nvarchar(max)'); Конечно, это все еще предполагает допустимый XML.
Если, например, закрывающий тег отсутствует, то это вызовет XML parsing ошибка.
Если HTML плохо сформирован как XML, то можно использовать PATINDEX & SUBSTRING, чтобы получить первый тег p. А затем приведите это к типу XML, чтобы получить значение.
select cast(SUBSTRING(@htmlData,patindex('%%',@htmlData),patindex('%
%',@htmlData) - patindex('%%',@htmlData)+4) as xml).value('.','nvarchar(max)');
или в стиле фанк рекурсивным способом:
declare @xmlData nvarchar(100); WITH Lines(n, x, y) AS ( SELECT 1, 1, CHARINDEX(char(13), @htmlData) UNION ALL SELECT n+1, y+1, CHARINDEX(char(13), @htmlData, y+1) FROM Lines WHERE y > 0 ) SELECT @xmlData = concat(@xmlData,SUBSTRING(@htmlData,x,IIF(y>0,y-x,8))) FROM Lines where PATINDEX('%%
%', SUBSTRING(@htmlData,x,IIF(y>0,y-x,10))) > 0 order by n; select @xmlData as xmlData, convert(XML,@xmlData,1).value('(/p/node())[1]', 'nvarchar(max)') as FirstP; Сначала создайте пользовательскую функцию, которая удаляет HTML-код следующим образом:
CREATE FUNCTION [dbo].[udf_StripHTML] (@HTMLText VARCHAR(MAX)) RETURNS VARCHAR(MAX) AS BEGIN DECLARE @Start INT; DECLARE @End INT; DECLARE @Length INT; SET @Start = CHARINDEX('', @HTMLText, CHARINDEX(' 0 AND @End > 0 AND @Length > 0 BEGIN SET @HTMLText = STUFF(@HTMLText, @Start, @Length, ''); SET @Start = CHARINDEX('', @HTMLText, CHARINDEX(' Когда вы пытаетесь выбрать его:
SELECT dbo.udf_StripHTML([column]) FROM SOMETABLE Это должно привести к тому, что вам не придется использовать несколько вложенных операторов замены.
Еще одно решение, просто чтобы продемонстрировать хитрость для замены многих значений таблицы (простота в обслуживании. ) в одном операторе:
--добавить любой заменить шаблоны здесь:
CREATE TABLE ReplaceTags (HTML VARCHAR(100)); INSERT INTO ReplaceTags VALUES (''),(''),(''),(''),('
') ,(''),(''),(''),('
'),(''); GO - эта функция будет выполнять "трюк"
CREATE FUNCTION dbo.DoReplace(@Content VARCHAR(MAX)) RETURNS VARCHAR(MAX) AS BEGIN SELECT @Content=REPLACE(@Content,HTML,'') FROM ReplaceTags; RETURN @Content; END GO - Все примеры, которые я нашел в вашем вопросе и в комментариях
DECLARE @content TABLE(Content VARCHAR(MAX)); INSERT INTO @content VALUES (' My text.
') ,(' My text.
More text.
') ,(' My text.
More text.
') ,(' My text.
'); SELECT dbo.DoReplace(Content) FROM @content; GO DROP FUNCTION dbo.DoReplace; DROP TABLE ReplaceTags; ОБНОВИТЬ
Если вы добавите значение замены в таблицу шаблонов, вы можете даже использовать другие значения в качестве замен, например, заменить
с фактическим переводом строки.
DECLARE @str VARCHAR(299) SELECT @str = ' My text.
' SELECT cast(@str AS XML).query('.').value('.', 'varchar(200)') SQL Server 2017+
Если у вас есть функция разделения строк, вы можете удалить HTML-теги практически из любого текста (правильно сформированного или нет):
select string_agg(c.String, null) within group (order by o.Ordinal) from dbo.SplitString(@Input, N'<') o cross apply dbo.SplitString(o.String, N'>') c where o.Ordinal = 1 or c.Ordinal = 2; Это будет столь же производительно, как и ваша функция разветвителя. Поэтому, как правило, он должен превосходить любое из решений на основе цикла.
Решения на основе замены не могут работать с комментариями или элементами, имеющими атрибуты, что делает их практически бесполезными для меня.
Вот мои версии функций разделения и разделения:
create or alter function dbo.SplitString ( @String nvarchar(max) , @Delimiter nvarchar(4000) ) returns table with schemabinding as return select Удалить html теги sql + 1 as Ordinal, value as String from openjson(replace(json_modify(N'[]', N'append $', @String), string_escape(@Delimiter, N'json'), N'","')) create or alter function dbo.StripHtml ( @Input nvarchar(max) ) returns nvarchar(max) as begin return ( select string_agg(c.String, null) within group (order by o.Ordinal) from dbo.SplitString(@Input, N'<') o cross apply dbo.SplitString(o.String, N'>') c where o.Ordinal = 1 or c.Ordinal = 2 ) end SQL - удалить все теги HTML в строке
В моем наборе данных есть поле, в котором хранится текст, размеченный HTML. Общий формат следующий:
Я мог бы попытаться решить проблему, выполнив следующие действия:
REPLACE(REPLACE(Table.HtmlData, ' ', ''), '
') Однако это не является строгим правилом, поскольку некоторые записи нарушают стандарты W3C и, например, не содержат тегов . Хуже того, могут отсутствовать закрывающие теги . Поэтому мне нужно было бы включить функцию REPLACE для каждого открывающего и закрывающего тега, который может существовать.
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( Table.HtmlData, '', ''), '', ''), '', ''), '', ''), '', ''), '', ''), '', ''), '
', '') Мне было интересно, есть ли лучший способ добиться этого, чем использование нескольких вложенных функций REPLACE . К сожалению, единственные языки, которые у меня доступны в этой среде, - это SQL и Visual Basic (не .NET).
7 ответов
DECLARE @x XML = ' My text.
' SELECT t.c.value('.', 'NVARCHAR(MAX)') FROM @x.nodes('*') t(c) Обновление - для строк с незакрытыми тегами:
DECLARE @x NVARCHAR(MAX) = ' My text.
More text.
' SELECT x.value('.', 'NVARCHAR(MAX)') FROM ( SELECT x = CAST(REPLACE(REPLACE(@x, '>', '/>'), ' SQL Server 2017+
Если у вас есть функция разделения строк, вы можете удалить HTML-теги практически из любого текста (правильно сформированного или нет):
select string_agg(c.String, null) within group (order by o.Ordinal) from dbo.SplitString(@Input, N'<') o cross apply dbo.SplitString(o.String, N'>') c where o.Ordinal = 1 or c.Ordinal = 2; Это будет столь же производительно, как и ваша функция разветвителя. Поэтому, как правило, он должен превосходить любые решения на основе цикла.
Решения на основе замены не могут работать с комментариями или элементами, имеющими атрибуты, что делает их практически бесполезными для меня.
Вот мои версии функций разделения и разделения:
create or alter function dbo.SplitString ( @String nvarchar(max) , @Delimiter nvarchar(4000) ) returns table with schemabinding as return select Удалить html теги sql + 1 as Ordinal, value as String from openjson(replace(json_modify(N'[]', N'append $', @String), string_escape(@Delimiter, N'json'), N'","')) create or alter function dbo.StripHtml ( @Input nvarchar(max) ) returns nvarchar(max) as begin return ( select string_agg(c.String, null) within group (order by o.Ordinal) from dbo.SplitString(@Input, N'<') o cross apply dbo.SplitString(o.String, N'>') c where o.Ordinal = 1 or c.Ordinal = 2 ) end Вы упомянули, что XML не всегда действителен, но всегда ли он содержит теги
В этом случае сработает следующее:
SUBSTRING(Table.HtmlData, CHARINDEX('', Table.HtmlData) + 1, CHARINDEX('
', Table.HtmlData) - CHARINDEX('', Table.HtmlData) + 1)
В качестве альтернативы я предлагаю использовать Visual Basic, как вы упомянули, это тоже вариант.
Сначала создайте пользовательскую функцию, которая удаляет HTML следующим образом:
CREATE FUNCTION [dbo].[udf_StripHTML] (@HTMLText VARCHAR(MAX)) RETURNS VARCHAR(MAX) AS BEGIN DECLARE @Start INT; DECLARE @End INT; DECLARE @Length INT; SET @Start = CHARINDEX('', @HTMLText, CHARINDEX(' 0 AND @End > 0 AND @Length > 0 BEGIN SET @HTMLText = STUFF(@HTMLText, @Start, @Length, ''); SET @Start = CHARINDEX('', @HTMLText, CHARINDEX(' Когда вы пытаетесь его выбрать:
SELECT dbo.udf_StripHTML([column]) FROM SOMETABLE Это должно привести к тому, что вам не придется использовать несколько вложенных операторов замены.
Это всего лишь пример. Вы можете использовать это в скрипте для удаления любых тегов html:
DECLARE @VALUE VARCHAR(MAX),@start INT,@end int,@remove varchar(max) SET @VALUE=' ' set @start=charindex('<',@value) while @start>0 begin set @end=charindex('>',@VALUE) set @remove=substring(@VALUE,@start,@end) set @value=replace(@value,@remove,'') set @start=charindex('<',@value) end print @value Если HTML-код правильно сформирован, нет необходимости использовать замену для синтаксического анализа XML.
Просто приведите или преобразуйте его в тип XML и получите значение (я).
Вот пример вывода текста из всех тегов:
declare @htmlData nvarchar(100) = ' My text.
My other text.
'; select convert(XML,@htmlData,1).value('.', 'nvarchar(max)'); select cast(@htmlData as XML).value('.', 'nvarchar(max)'); Обратите внимание, что существует разница в выводе пробелов между приведением и преобразованием.
Чтобы получить контент только с определенного узла, используется синтаксис XQuery . (XQuery основан на синтаксисе XPath)
select cast(@htmlData as XML).value('(//body/p/node())[1]', 'nvarchar(max)'); select convert(XML,@htmlData,1).value('(//body/p/node())[1]', 'nvarchar(max)'); Конечно, при этом по-прежнему предполагается действующий XML.
Если, например, отсутствует закрывающий тег, это вызовет ошибку XML parsing .
Если HTML неправильно сформирован как XML, можно использовать PATINDEX и SUBSTRING для получения первого тега p. А затем приведите его к типу XML, чтобы получить значение.
select cast(SUBSTRING(@htmlData,patindex('%%',@htmlData),patindex('%
%',@htmlData) - patindex('%%',@htmlData)+4) as xml).value('.','nvarchar(max)');
Или через забавный рекурсивный способ:
declare @xmlData nvarchar(100); WITH Lines(n, x, y) AS ( SELECT 1, 1, CHARINDEX(char(13), @htmlData) UNION ALL SELECT n+1, y+1, CHARINDEX(char(13), @htmlData, y+1) FROM Lines WHERE y > 0 ) SELECT @xmlData = concat(@xmlData,SUBSTRING(@htmlData,x,IIF(y>0,y-x,8))) FROM Lines where PATINDEX('%%
%', SUBSTRING(@htmlData,x,IIF(y>0,y-x,10))) > 0 order by n; select @xmlData as xmlData, convert(XML,@xmlData,1).value('(/p/node())[1]', 'nvarchar(max)') as FirstP; Еще одно решение, просто чтобы продемонстрировать трюк для замены множества значений таблицы ( легко поддерживать . ) в одном операторе:
- добавьте сюда любые шаблоны замены:
CREATE TABLE ReplaceTags (HTML VARCHAR(100)); INSERT INTO ReplaceTags VALUES (''),(''),(''),(''),('
') ,(''),(''),(''),('
'),(''); GO - Эта функция выполнит «фокус»
CREATE FUNCTION dbo.DoReplace(@Content VARCHAR(MAX)) RETURNS VARCHAR(MAX) AS BEGIN SELECT @Content=REPLACE(@Content,HTML,'') FROM ReplaceTags; RETURN @Content; END GO - Все примеры, которые я нашел в вашем вопросе и в комментариях
DECLARE @content TABLE(Content VARCHAR(MAX)); INSERT INTO @content VALUES (' My text.
') ,(' My text.
More text.
') ,(' My text.
More text.
') ,(' My text.
'); SELECT dbo.DoReplace(Content) FROM @content; GO DROP FUNCTION dbo.DoReplace; DROP TABLE ReplaceTags; ОБНОВИТЬ
Если вы добавляете заменяющее значение в таблицу-шаблон, вы даже можете использовать другие значения в качестве замены, например, заменить
фактическим разрывом строки .