SQL Server: Writing an HTML encode function

Sometimes we need to extract a text from the database to show it in a web page. The text should be showed as is, including any HTML tags and special characters. Normally the encoding is done by a web application, but there are cases when an SQL string is expected to return HTML encoded texts.

HTML encoding

Encoding a text as HTML means to replace special characters with sequences of characters starting with "&". Also, note that all HTML tags start with a "<" character.

For this reason, instead of escaping all special characters, we can just escape "&" and "<". Any web browser will be able to display the text correctly.

SQL syntax

To replace these characters, we can use the REPLACE() function:

REPLACE(txt, '&', '&amp;')
REPLACE(txt, '<', '&lt;')

Let’s put them together:

SELECT
    REPLACE(
        REPLACE(
            title,
            '&',
            '&amp;'
        ),
        '<',
        '&lt;'
    ) AS title
    FROM blog_posts;

The order of replacements is important. Replacing a "<" (or any other special character) will produce a new "&", that doesn’t need to be escaped. So we need to replace "&" first.

Writing a stored function

The syntax above is verbose and very error-prone. To avoid it, it could be a good idea to write a stored function instead:

CREATE FUNCTION html_encode(@txt AS TEXT)
    RETURNS TEXT
AS
    BEGIN
        RETURN REPLACE(
            REPLACE(
                @txt,
                '&',
                '&amp;'
            ),
            '<',
            '&lt;'
        );
END
GO

To use this functions:

SELECT html_encode(title) AS title
    FROM blog_posts;