DELIMITER //

CREATE FUNCTION CleanText(input TEXT) RETURNS TEXT
BEGIN
    DECLARE output TEXT;

    -- Remove HTML tags
    SET output = REGEXP_REPLACE(input, '<[^>]*>', '');

    -- Handle special character entities
    SET output = REPLACE(output, '&nbsp;', ' ');
    SET output = REPLACE(output, '&amp;', '&');
    SET output = REPLACE(output, '&lt;', '<');
    SET output = REPLACE(output, '&gt;', '>');
    SET output = REPLACE(output, '&quot;', '"');
    SET output = REPLACE(output, '&ldquo;', '“');
    SET output = REPLACE(output, '&rdquo;', '”');
    SET output = REPLACE(output, '&lsquo;', '‘');
    SET output = REPLACE(output, '&rsquo;', '’');
    SET output = REPLACE(output, '&mdash;', '—');
    SET output = REPLACE(output, '&ndash;', '–');
    SET output = REPLACE(output, '&hellip;', '…');
    SET output = REPLACE(output, '&bull;', '•');

    -- Trim excessive whitespace
    SET output = TRIM(REGEXP_REPLACE(output, '[[:space:]]{2,}', ' '));

    RETURN output;
END//

DELIMITER ;