Shape / columns · All operations

Turn HTML into plain text

A variant of Clean up text

Remove HTML tags from text and decode its character entities.

Status

In arcform

Runs as a SQL step

arcform carries it as a sql: step today.

The DuckDB step

Remove HTML tags and decode named, decimal and hex entities in one pass

SQL

CREATE TEMP TABLE raw AS SELECT '<p>Tom &amp; <b>Jerry</b> &lt;3 &quot;cheese&quot;&nbsp;&#65;&#x42;&#x1F600; R&D &#38;amp; &#1114112;&#xD800;</p>' AS html;
-- The text is cut into entities and the stretches between them, and each piece is decoded once, so &#38;amp; gives &amp;
-- and not &. A numeric entity chr() cannot encode (above U+10FFFF, or a surrogate) is left as written.
CREATE TEMP TABLE result AS
SELECT array_to_string(list_transform(regexp_extract_all(regexp_replace(html, '<[^>]*>', '', 'g'), '&[#a-zA-Z0-9]+;|[^&]+|&'),
  lambda t: CASE
    WHEN t = '&lt;' THEN '<' WHEN t = '&gt;' THEN '>' WHEN t = '&quot;' THEN '"' WHEN t = '&nbsp;' THEN chr(160) WHEN t = '&amp;' THEN '&'
    WHEN regexp_matches(t, '^&#[0-9]+;$') AND TRY_CAST(t[3:length(t) - 1] AS BIGINT) BETWEEN 1 AND 1114111
         AND NOT TRY_CAST(t[3:length(t) - 1] AS BIGINT) BETWEEN 55296 AND 57343 THEN chr(t[3:length(t) - 1]::INT)
    WHEN regexp_matches(t, '^&#[xX][0-9a-fA-F]+;$') AND TRY_CAST('0x' || t[4:length(t) - 1] AS BIGINT) BETWEEN 1 AND 1114111
         AND NOT TRY_CAST('0x' || t[4:length(t) - 1] AS BIGINT) BETWEEN 55296 AND 57343 THEN chr(('0x' || t[4:length(t) - 1])::INT)
    ELSE t END), '') AS plain
FROM raw;
SELECT * FROM result;
SELECT CASE WHEN ((SELECT plain FROM result) = 'Tom & Jerry <3 "cheese"' || chr(160) || 'AB' || chr(128512) || ' R&D &amp; &#1114112;&#xD800;') THEN 'as expected' ELSE error('the HTML did not become plain text with each entity decoded once and the ones chr cannot encode left as written') END AS verdict;

The statement builds its own data.
It needs nothing installed beyond DuckDB itself.
The statement checks its own result and raises an error when it is wrong: “the HTML did not become plain text with each entity decoded once and the ones chr cannot encode left as written”.
It passed on DuckDB v2.0.0 (Cyanoptera) d54cbbe3df.

What other tools call it

Meridian's classification; an independent blind rating agreed on 95.7% of 324 rows (variant, version 3).

Apache Hop (1)

HTML 2 Textread 2026-09-27
Strip HTML markup from a field, leaving the text content

Duckle (1)

Strip HTMLread 2026-09-25
remove HTML tags by regex

OpenRefine (1)

Unescape HTML entitiesread 2026-09-27
Replace HTML character references with the characters they stand for

RapidMiner Studio (2)

Unescape HTMLread 2026-09-27
Decode HTML escape sequences in column values
Unescape HTML Documentread 2026-09-27
Decode HTML escape sequences in a document

Background

Apache Hop's HTML 2 Text transform parses an HTML page and converts its content to text.^[1] HTML can encode a character as a numeric character reference, decimal or hexadecimal, or as a named character entity reference.^[2] Python's html.unescape converts all named and numeric character references, such as >, > and >, to the Unicode characters they stand for.^[3]

When to use it

Data from an HTML-formatted source may express some characters through references such as   for a space, instead of the Unicode characters themselves.^[4] Remove the tags first and decode the references afterwards, once only, so that an escaped <b> in the text survives as the characters <b> rather than being taken for a tag. (Meridian's view)

In the HTML Standard, script and style are raw text elements, which can have text, while normal elements can also hold character references and other elements.^[5] Drop script and style elements together with their content before removing the other tags. (Meridian's view) After decoding, look for non-breaking spaces before trimming or splitting the text. (Meridian's view)

The method

HTML's use of character references derives from the Standard Generalized Markup Language (SGML).^[2] In the HTML Standard, a named character reference is an ampersand, one of the listed names in the same case, and a terminating semicolon.^[5] In the HTML Standard, a numeric reference is an ampersand and a number sign followed by decimal digits, or by x or X and hexadecimal digits, and then a semicolon.^[5] The HTML Standard allows a numeric character reference to name any code point except carriage return, noncharacters, and controls other than ASCII whitespace.^[5] Python's html.unescape applies the HTML 5 rules for both valid and invalid character references, with the HTML 5 list of named references.^[3]

Compared with the alternatives

XML predefines only five character entity references, and an HTML name such as é is an error in an XML document unless the entity has been defined.^[2] OpenRefine's unescape function takes a mode, one of html, xml, csv, url or javascript, naming the escaping it undoes.^[6] Decode with the rules of the format the text came from. (Meridian's view)

Sources

[1]HTML 2 Text Transform | Apache Hopdocumentation
[2]Character encodings in HTML - Wikipediasecondary source
[3]html — HyperText Markup Language support — Python 3.15.2 documentationdocumentation
[4]Cell editing | OpenRefinedocumentation
[5]HTML Standardstandard
[6]GREL functions | OpenRefinedocumentation