В моем наборе данных у меня есть поле, в котором хранится текст, помеченный HTML. Общий формат выглядит следующим образом:
<html><head></head><body><p>My text.</p></body></html>
Я мог бы попытаться решить проблему, выполнив следующие действия:
REPLACE(REPLACE(Table.HtmlData, '<html><head></head><body><p>', ''), '</p></body></html>')
Однако это не строгое правило, так как некоторые из записей нарушают стандарты W3C и не включают теги <head>, например. Хуже того, могут отсутствовать закрывающие теги. Поэтому мне нужно включить функцию REPLACE для каждого открытого и закрывающего тега, который может существовать.
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
Table.HtmlData,
'<html>', ''),
'</html>', ''),
'<head>', ''),
'</head>', ''),
'<body>', ''),
'</body>', ''),
'<p>', ''),
'</p>', '')
Мне было интересно, есть ли лучший способ сделать это, чем использовать несколько вложенных функций REPLACE. К сожалению, единственными языками, которые я имею в этой среде, являются SQL и Visual Basic (а не .NET).
DECLARE @x XML = '<html><head></head><body><p>My text.</p></body></html>'
SELECT t.c.value('.', 'NVARCHAR(MAX)')
FROM @x.nodes('*') t(c)
Обновление - для строк с закрытыми тегами:
DECLARE @x NVARCHAR(MAX) = '<html><head></head><body><p>My text.<br>More text.</p></body></html>'
SELECT x.value('.', 'NVARCHAR(MAX)')
FROM (
SELECT x = CAST(REPLACE(REPLACE(@x, '>', '/>'), '</', '<') AS XML)
) r
Если HTML хорошо сформирован, нет необходимости использовать замену для синтаксического анализа XML.
Просто введите или преобразуйте его в тип XML и получите значения.
Вот пример вывода текста из всех тегов:
declare @htmlData nvarchar(100) = '<html>
<head>
</head>
<body>
<p>My text.</p>
<p>My other text.</p>
</body>
</html>';
select convert(XML,@htmlData,1).value('.', 'nvarchar(max)');
select cast(@htmlData as XML).value('.', 'nvarchar(max)');
Обратите внимание, что есть разница в выходе пробела между литьем и конвертированием.
Чтобы получать контент только из определенного node, используется синтаксис 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)');
Результат: My text.
Конечно, это все равно предполагает действительный XML.
Если, например, закрывающий тег отсутствует, это приведет к возникновению ошибки XML parsing.
Если HTML не сформирован как XML, тогда можно использовать PATINDEX и SUBSTRING для получения первого p-тега. А затем примените это к типу XML, чтобы получить значение.
select cast(SUBSTRING(@htmlData,patindex('%<p>%',@htmlData),patindex('%</p>%',@htmlData) - patindex('%<p>%',@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('%<p>%</p>%', 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);
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
Когда вы пытаетесь выбрать его:
SELECT dbo.udf_StripHTML([column]) FROM SOMETABLE
Это должно привести к тому, что вам не придется использовать несколько вложенных операторов замены.
Кредитная и дополнительная информация: http://blog.sqlauthority.com/2007/06/16/sql-server-udf-user-defined-function-to-strip-html-parse-html-no-regular-expression/
Еще одно решение, просто чтобы продемонстрировать трюк, чтобы заменить многие значения таблицы (легко поддерживать!!!) в одном объявлении:
- добавьте любые шаблоны замены здесь:
CREATE TABLE ReplaceTags (HTML VARCHAR(100));
INSERT INTO ReplaceTags VALUES
('<html>'),('<head>'),('<body>'),('<p>'),('<br>')
,('</html>'),('</head>'),('</body>'),('</p>'),('</br>');
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
('<html><head></head><body><p>My text.</p></body></html>')
,('<html><head></head><body><p>My text.<br>More text.</p></body></html>')
,('<html><head></head><body><p>My text.<br>More text.</p></body></html>')
,('<html><head></head><body><p>My text.</p></html>');
- это фактический запрос
SELECT dbo.DoReplace(Content) FROM @content;
GO
- Clean-Up
DROP FUNCTION dbo.DoReplace;
DROP TABLE ReplaceTags;
Если вы добавите значение замены в таблицу шаблонов, вы можете использовать разные значения в качестве замены, например, заменить a <br> на фактический разрыв строки...
Это просто пример. Вы можете использовать это в script для rmeove любых тегов html:
DECLARE @VALUE VARCHAR(MAX),@start INT,@end int,@remove varchar(max)
SET @VALUE='<html itemscope itemtype="http://schema.org/QAPage">
<head>
<title>sql - Converting INT to DATE then using GETDATE on conversion? - Stack Overflow</title>
<html>
</html>
'
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
Это самый простой способ.
DECLARE @str VARCHAR(299)
SELECT @str = '<html><head></head><body><p>My text.</p></body></html>'
SELECT cast(@str AS XML).query('.').value('.', 'varchar(200)')
Вы упомянули, что XML не всегда корректен, но всегда ли он содержит теги
и
?В этом случае будет работать следующее:
SUBSTRING(Table.HtmlData,
CHARINDEX('<p>', Table.HtmlData) + 1,
CHARINDEX('</p>', Table.HtmlData) - CHARINDEX('<p>', Table.HtmlData) + 1)
Для нахождения всех позиций
внутри HTML, здесь уже есть хорошая статья: https://dba.stackexchange.com/questions/41961/how-to-find-all-positions-of-a-string-within-another-string
В качестве альтернативы я предлагаю использовать Visual Basic, как вы уже упоминали, это также вариант.
SSMS 2017 имеет встроенную функцию поиска и замены регулярным выражением, но я не выяснил, как это сделать с помощью запроса.
Некоторые ответы здесь используют XML parsing, но этот метод работает только для простых строк html и возвращает ошибку: XML parsing: line 5, character 6, end tag does not match start tag со сложными строками html, как этот, который мы получаем из системы службы поддержки.
'<!--html--><div>
<div>A sentence.</div>
<br>
<div>Thank you!</div>
</div>'
Я закончил тем, что использовал рекурсивный CTE, чтобы выполнить работу. Вот моя функция.
ALTER FUNCTION [dbo].[udf_removeHtmlTags]
(
@html NVARCHAR(MAX)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
DECLARE @returnHtml NVARCHAR(MAX)
;with cte(id, html) as (
select 1, STUFF(@html, CHARINDEX('<', @html), CHARINDEX('>', @html)-CHARINDEX('<', @html)+1, '')
union all
select id + 1, STUFF(html, CHARINDEX('<', html), CHARINDEX('>', html)-CHARINDEX('<', html)+1, '')
from cte
where html like '%<%>%'
)
select top 1 @returnHtml = html
from cte
order by id desc
RETURN @returnHtml
END