sqlserver - ¿La mejor manera de quitar las etiquetas html de una cadena en el servidor sql?

substring string sql server

Question

Tengo datos en SQL Server 2005 que contienen etiquetas html y me gustaría quitar todo eso, dejando solo el texto entre las etiquetas. Lo ideal es también reemplazar cosas como < con < , etc.

¿Hay una manera fácil de hacer esto o alguien ya tiene un código t-sql de muestra?

No tengo la capacidad de agregar procesos almacenados extendidos y similares, por lo que preferiría un enfoque de t-sql puro (preferiblemente uno compatible con sql 2000).

Solo quiero recuperar los datos con html eliminado, no actualizarlos, por lo que idealmente se escribiría como una función definida por el usuario, para facilitar su reutilización.

Entonces, por ejemplo, convertir esto:

<B>Some useful text</B>&nbsp;
<A onclick="return openInfo(this)"
href="http://there.com/3ce984e88d0531bac5349"
target=globalhelp>
<IMG title="Source Description" height=15 alt="Source Description"
src="/ri/new_info.gif" width=15 align=top border=0>
</A>&gt;&nbsp;<b>more text</b></TD></TR>

a esto:

Some useful text > more text

Answer 1

¿Qué le parece usar XQuery con un trazador de líneas uno:

select @xml.query(''for $x in //. return ($x)//text()'')

Esto recorre todos los elementos y devuelve el texto () solamente.

Para evitar texto entre elementos concatenados sin espacios, use:

SELECT @xml.query(''for $x in //. return concat((($x)//text())[1]," ")'')

Estos son excelentes para cuando quiere construir frases de búsqueda, tira de HTML, etc.

Solo tenga en cuenta que esto devuelve tipo xml, por lo tanto, CAST o COVERT para enviar el texto donde corresponda. La versión xml de este tipo de datos es inútil, ya que no es un XML bien formado.

Answer 2

Aquí hay una versión actualizada de esta función que incorpora la respuesta de RedFilter (original de Pinal) con las adiciones de LazyCoders y las correcciones de errores de tipo goodeye Y mi propia adición para manejar etiquetas en línea <STYLE> dentro del HTML.

ALTER FUNCTION [dbo].[udf_StripHTML]
(
@HTMLText varchar(MAX)
)
RETURNS varchar(MAX)
AS
BEGIN
DECLARE @Start int
DECLARE @End int
DECLARE @Length int
-- Replace the HTML entity & with the ''&'' character (this needs to be done first, as
-- ''&'' might be double encoded as ''&'')
SET @Start = CHARINDEX(''&'', @HTMLText)
SET @End = @Start + 4
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 = @Start + 4
SET @Length = (@End - @Start) + 1
END
-- Replace the HTML entity &lt; with the ''<'' character
SET @Start = CHARINDEX(''&lt;'', @HTMLText)
SET @End = @Start + 3
SET @Length = (@End - @Start) + 1
WHILE (@Start > 0 AND @End > 0 AND @Length > 0) BEGIN
SET @HTMLText = STUFF(@HTMLText, @Start, @Length, ''<'')
SET @Start = CHARINDEX(''&lt;'', @HTMLText)
SET @End = @Start + 3
SET @Length = (@End - @Start) + 1
END
-- Replace the HTML entity &gt; with the ''>'' character
SET @Start = CHARINDEX(''&gt;'', @HTMLText)
SET @End = @Start + 3
SET @Length = (@End - @Start) + 1
WHILE (@Start > 0 AND @End > 0 AND @Length > 0) BEGIN
SET @HTMLText = STUFF(@HTMLText, @Start, @Length, ''>'')
SET @Start = CHARINDEX(''&gt;'', @HTMLText)
SET @End = @Start + 3
SET @Length = (@End - @Start) + 1
END
-- Replace the HTML entity &amp; with the ''&'' character
SET @Start = CHARINDEX(''&amp;amp;'', @HTMLText)
SET @End = @Start + 4
SET @Length = (@End - @Start) + 1
WHILE (@Start > 0 AND @End > 0 AND @Length > 0) BEGIN
SET @HTMLText = STUFF(@HTMLText, @Start, @Length, ''&'')
SET @Start = CHARINDEX(''&amp;amp;'', @HTMLText)
SET @End = @Start + 4
SET @Length = (@End - @Start) + 1
END
-- Replace the HTML entity &nbsp; with the '' '' character
SET @Start = CHARINDEX(''&nbsp;'', @HTMLText)
SET @End = @Start + 5
SET @Length = (@End - @Start) + 1
WHILE (@Start > 0 AND @End > 0 AND @Length > 0) BEGIN
SET @HTMLText = STUFF(@HTMLText, @Start, @Length, '' '')
SET @Start = CHARINDEX(''&nbsp;'', @HTMLText)
SET @End = @Start + 5
SET @Length = (@End - @Start) + 1
END
-- Replace any <br> tags with a newline
SET @Start = CHARINDEX(''<br>'', @HTMLText)
SET @End = @Start + 3
SET @Length = (@End - @Start) + 1
WHILE (@Start > 0 AND @End > 0 AND @Length > 0) BEGIN
SET @HTMLText = STUFF(@HTMLText, @Start, @Length, CHAR(13) + CHAR(10))
SET @Start = CHARINDEX(''<br>'', @HTMLText)
SET @End = @Start + 3
SET @Length = (@End - @Start) + 1
END
-- Replace any <br/> tags with a newline
SET @Start = CHARINDEX(''<br/>'', @HTMLText)
SET @End = @Start + 4
SET @Length = (@End - @Start) + 1
WHILE (@Start > 0 AND @End > 0 AND @Length > 0) BEGIN
SET @HTMLText = STUFF(@HTMLText, @Start, @Length, CHAR(13) + CHAR(10))
SET @Start = CHARINDEX(''<br/>'', @HTMLText)
SET @End = @Start + 4
SET @Length = (@End - @Start) + 1
END
-- Replace any <br /> tags with a newline
SET @Start = CHARINDEX(''<br />'', @HTMLText)
SET @End = @Start + 5
SET @Length = (@End - @Start) + 1
WHILE (@Start > 0 AND @End > 0 AND @Length > 0) BEGIN
SET @HTMLText = STUFF(@HTMLText, @Start, @Length, CHAR(13) + CHAR(10))
SET @Start = CHARINDEX(''<br />'', @HTMLText)
SET @End = @Start + 5
SET @Length = (@End - @Start) + 1
END
-- Remove anything between <STYLE> tags
SET @Start = CHARINDEX(''<STYLE'', @HTMLText)
SET @End = CHARINDEX(''</STYLE>'', @HTMLText, CHARINDEX(''<'', @HTMLText)) + 7
SET @Length = (@End - @Start) + 1
WHILE (@Start > 0 AND @End > 0 AND @Length > 0) BEGIN
SET @HTMLText = STUFF(@HTMLText, @Start, @Length, '''')
SET @Start = CHARINDEX(''<STYLE'', @HTMLText)
SET @End = CHARINDEX(''</STYLE>'', @HTMLText, CHARINDEX(''</STYLE>'', @HTMLText)) + 7
SET @Length = (@End - @Start) + 1
END
-- Remove anything between <whatever> tags
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

Answer 3

Derivado de @Goner Doug answer, con algunas cosas actualizadas:
- usando REEMPLAZAR cuando sea posible
- conversión de entidades predefinidas como é (Elegí las que necesitaba :-)
- Alguna conversión de etiquetas de lista <ul> and <li>

ALTER FUNCTION [dbo].[udf_StripHTML]
--by Patrick Honorez --- www.idevlop.com
--inspired by http://.com/questions/457701/best-way-to-strip-html-tags-from-a-string-in-sql-server/39253602#39253602
(
@HTMLText varchar(MAX)
)
RETURNS varchar(MAX)
AS
BEGIN
DECLARE @Start int
DECLARE @End int
DECLARE @Length int
set @HTMLText = replace(@htmlText, ''<br>'',CHAR(13) + CHAR(10))
set @HTMLText = replace(@htmlText, ''<br/>'',CHAR(13) + CHAR(10))
set @HTMLText = replace(@htmlText, ''<br />'',CHAR(13) + CHAR(10))
set @HTMLText = replace(@htmlText, ''<li>'',''- '')
set @HTMLText = replace(@htmlText, ''</li>'',CHAR(13) + CHAR(10))
set @HTMLText = replace(@htmlText, ''&rsquo;'' collate Latin1_General_CS_AS, '''''''' collate Latin1_General_CS_AS)
set @HTMLText = replace(@htmlText, ''&quot;'' collate Latin1_General_CS_AS, ''"'' collate Latin1_General_CS_AS)
set @HTMLText = replace(@htmlText, ''&amp;'' collate Latin1_General_CS_AS, ''&'' collate Latin1_General_CS_AS)
set @HTMLText = replace(@htmlText, ''&euro;'' collate Latin1_General_CS_AS, ''€'' collate Latin1_General_CS_AS)
set @HTMLText = replace(@htmlText, ''&lt;'' collate Latin1_General_CS_AS, ''<'' collate Latin1_General_CS_AS)
set @HTMLText = replace(@htmlText, ''&gt;'' collate Latin1_General_CS_AS, ''>'' collate Latin1_General_CS_AS)
set @HTMLText = replace(@htmlText, ''&oelig;'' collate Latin1_General_CS_AS, ''oe'' collate Latin1_General_CS_AS)
set @HTMLText = replace(@htmlText, ''&nbsp;'' collate Latin1_General_CS_AS, '' '' collate Latin1_General_CS_AS)
set @HTMLText = replace(@htmlText, ''&copy;'' collate Latin1_General_CS_AS, ''©'' collate Latin1_General_CS_AS)
set @HTMLText = replace(@htmlText, ''&laquo;'' collate Latin1_General_CS_AS, ''«'' collate Latin1_General_CS_AS)
set @HTMLText = replace(@htmlText, ''&reg;'' collate Latin1_General_CS_AS, ''®'' collate Latin1_General_CS_AS)
set @HTMLText = replace(@htmlText, ''&plusmn;'' collate Latin1_General_CS_AS, ''±'' collate Latin1_General_CS_AS)
set @HTMLText = replace(@htmlText, ''&sup2;'' collate Latin1_General_CS_AS, ''²'' collate Latin1_General_CS_AS)
set @HTMLText = replace(@htmlText, ''&sup3;'' collate Latin1_General_CS_AS, ''³'' collate Latin1_General_CS_AS)
set @HTMLText = replace(@htmlText, ''&micro;'' collate Latin1_General_CS_AS, ''µ'' collate Latin1_General_CS_AS)
set @HTMLText = replace(@htmlText, ''&middot;'' collate Latin1_General_CS_AS, ''·'' collate Latin1_General_CS_AS)
set @HTMLText = replace(@htmlText, ''&ordm;'' collate Latin1_General_CS_AS, ''º'' collate Latin1_General_CS_AS)
set @HTMLText = replace(@htmlText, ''&raquo;'' collate Latin1_General_CS_AS, ''»'' collate Latin1_General_CS_AS)
set @HTMLText = replace(@htmlText, ''&frac14;'' collate Latin1_General_CS_AS, ''¼'' collate Latin1_General_CS_AS)
set @HTMLText = replace(@htmlText, ''&frac12;'' collate Latin1_General_CS_AS, ''½'' collate Latin1_General_CS_AS)
set @HTMLText = replace(@htmlText, ''&frac34;'' collate Latin1_General_CS_AS, ''¾'' collate Latin1_General_CS_AS)
set @HTMLText = replace(@htmlText, ''&Aelig'' collate Latin1_General_CS_AS, ''Æ'' collate Latin1_General_CS_AS)
set @HTMLText = replace(@htmlText, ''&Ccedil;'' collate Latin1_General_CS_AS, ''Ç'' collate Latin1_General_CS_AS)
set @HTMLText = replace(@htmlText, ''&Egrave;'' collate Latin1_General_CS_AS, ''È'' collate Latin1_General_CS_AS)
set @HTMLText = replace(@htmlText, ''&Eacute;'' collate Latin1_General_CS_AS, ''É'' collate Latin1_General_CS_AS)
set @HTMLText = replace(@htmlText, ''&Ecirc;'' collate Latin1_General_CS_AS, ''Ê'' collate Latin1_General_CS_AS)
set @HTMLText = replace(@htmlText, ''&Ouml;'' collate Latin1_General_CS_AS, ''Ö'' collate Latin1_General_CS_AS)
set @HTMLText = replace(@htmlText, ''&agrave;'' collate Latin1_General_CS_AS, ''à'' collate Latin1_General_CS_AS)
set @HTMLText = replace(@htmlText, ''&acirc;'' collate Latin1_General_CS_AS, ''â'' collate Latin1_General_CS_AS)
set @HTMLText = replace(@htmlText, ''&auml;'' collate Latin1_General_CS_AS, ''ä'' collate Latin1_General_CS_AS)
set @HTMLText = replace(@htmlText, ''&aelig;'' collate Latin1_General_CS_AS, ''æ'' collate Latin1_General_CS_AS)
set @HTMLText = replace(@htmlText, ''&ccedil;'' collate Latin1_General_CS_AS, ''ç'' collate Latin1_General_CS_AS)
set @HTMLText = replace(@htmlText, ''&egrave;'' collate Latin1_General_CS_AS, ''è'' collate Latin1_General_CS_AS)
set @HTMLText = replace(@htmlText, ''&eacute;'' collate Latin1_General_CS_AS, ''é'' collate Latin1_General_CS_AS)
set @HTMLText = replace(@htmlText, ''&ecirc;'' collate Latin1_General_CS_AS, ''ê'' collate Latin1_General_CS_AS)
set @HTMLText = replace(@htmlText, ''&euml;'' collate Latin1_General_CS_AS, ''ë'' collate Latin1_General_CS_AS)
set @HTMLText = replace(@htmlText, ''&icirc;'' collate Latin1_General_CS_AS, ''î'' collate Latin1_General_CS_AS)
set @HTMLText = replace(@htmlText, ''&ocirc;'' collate Latin1_General_CS_AS, ''ô'' collate Latin1_General_CS_AS)
set @HTMLText = replace(@htmlText, ''&ouml;'' collate Latin1_General_CS_AS, ''ö'' collate Latin1_General_CS_AS)
set @HTMLText = replace(@htmlText, ''&divide;'' collate Latin1_General_CS_AS, ''÷'' collate Latin1_General_CS_AS)
set @HTMLText = replace(@htmlText, ''&oslash;'' collate Latin1_General_CS_AS, ''ø'' collate Latin1_General_CS_AS)
set @HTMLText = replace(@htmlText, ''&ugrave;'' collate Latin1_General_CS_AS, ''ù'' collate Latin1_General_CS_AS)
set @HTMLText = replace(@htmlText, ''&uacute;'' collate Latin1_General_CS_AS, ''ú'' collate Latin1_General_CS_AS)
set @HTMLText = replace(@htmlText, ''&ucirc;'' collate Latin1_General_CS_AS, ''û'' collate Latin1_General_CS_AS)
set @HTMLText = replace(@htmlText, ''&uuml;'' collate Latin1_General_CS_AS, ''ü'' collate Latin1_General_CS_AS)
set @HTMLText = replace(@htmlText, ''&quot;'' collate Latin1_General_CS_AS, ''"'' collate Latin1_General_CS_AS)
set @HTMLText = replace(@htmlText, ''&amp;'' collate Latin1_General_CS_AS, ''&'' collate Latin1_General_CS_AS)
set @HTMLText = replace(@htmlText, ''&lsaquo;'' collate Latin1_General_CS_AS, ''<'' collate Latin1_General_CS_AS)
set @HTMLText = replace(@htmlText, ''&rsaquo;'' collate Latin1_General_CS_AS, ''>'' collate Latin1_General_CS_AS)
-- Remove anything between <STYLE> tags
SET @Start = CHARINDEX(''<STYLE'', @HTMLText)
SET @End = CHARINDEX(''</STYLE>'', @HTMLText, CHARINDEX(''<'', @HTMLText)) + 7
SET @Length = (@End - @Start) + 1
WHILE (@Start > 0 AND @End > 0 AND @Length > 0) BEGIN
SET @HTMLText = STUFF(@HTMLText, @Start, @Length, '''')
SET @Start = CHARINDEX(''<STYLE'', @HTMLText)
SET @End = CHARINDEX(''</STYLE>'', @HTMLText, CHARINDEX(''</STYLE>'', @HTMLText)) + 7
SET @Length = (@End - @Start) + 1
END
-- Remove anything between <whatever> tags
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

Answer 4

Esta no es una solución completamente nueva, sino una corrección para la solución del prestador de servicios :

--note comments to see the corrections
CREATE FUNCTION [dbo].[StripHTML] (@HTMLText VARCHAR(MAX))
RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE @Start INT
DECLARE @End INT
DECLARE @Length INT
--DECLARE @TempStr varchar(255) (this is not used)
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
IF (UPPER(SUBSTRING(@HTMLText, @Start, 4)) <> ''<BR>'') AND (UPPER(SUBSTRING(@HTMLText, @Start, 5)) <> ''</BR>'')
begin
SET @HTMLText = STUFF(@HTMLText,@Start,@Length,'''')
end
-- this ELSE and SET is important
ELSE
SET @Length = 0;
-- minus @Length here below is important
SET @Start = CHARINDEX(''<'',@HTMLText, @End-@Length)
SET @End = CHARINDEX(''>'',@HTMLText,CHARINDEX(''<'',@HTMLText, @Start))
-- instead of -1 it should be +1
SET @Length = (@End - @Start) + 1
END
RETURN RTRIM(LTRIM(@HTMLText))
END

Answer 5

Hay un UDF que hará lo que se describe aquí:

Función definida por el usuario para eliminar 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

Editar: tenga en cuenta que esto es para SQL Server 2005, pero si cambia la palabra clave MAX a algo así como 4000, también funcionará en SQL Server 2000.

Answer 6

Prueba esto. Es una versión modificada de la publicada por RedFilter ... este SQL elimina todas las etiquetas excepto BR, B y P con los atributos que se acompañan:

CREATE FUNCTION [dbo].[StripHtml] (@HTMLText VARCHAR(MAX))
RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE @Start INT
DECLARE @End INT
DECLARE @Length INT
DECLARE @TempStr varchar(255)
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
IF (UPPER(SUBSTRING(@HTMLText, @Start, 3)) <> ''<BR'') AND (UPPER(SUBSTRING(@HTMLText, @Start, 2)) <> ''<P'') AND (UPPER(SUBSTRING(@HTMLText, @Start, 2)) <> ''<B'') AND (UPPER(SUBSTRING(@HTMLText, @Start, 3)) <> ''</B'')
BEGIN
SET @HTMLText = STUFF(@HTMLText,@Start,@Length,'''')
END
SET @Start = CHARINDEX(''<'',@HTMLText, @End)
SET @End = CHARINDEX(''>'',@HTMLText,CHARINDEX(''<'',@HTMLText, @Start))
SET @Length = (@End - @Start) - 1
END
RETURN RTRIM(LTRIM(@HTMLText))
END

Answer 7

Si su HTML está bien formado, creo que esta es una mejor solución:

create function dbo.StripHTML( @text varchar(max) ) returns varchar(max) as
begin
declare @textXML xml
declare @result varchar(max)
set @textXML = REPLACE( @text, ''&'', '''' );
with doc(contents) as
(
select chunks.chunk.query(''.'') from @textXML.nodes(''/'') as chunks(chunk)
)
select @result = contents.value(''.'', ''varchar(max)'') from doc
return @result
end
go
select dbo.StripHTML(''This <i>is</i> an <b>html</b> test'')