Validating an HTML input string

Question

A partial solution is to get all the Notes (VARCHAR) where there is an imbalance in < and >. But how can I write a query for that?

For example:

Input: <html>Hello World</html>
Output: Valid HTML

Input: <html><Hello World</html>
Output: Invalid HTML

Input: <html><Hello World></html>
Output: Valid HTML (Marking it Valid is OK for my use case)
But hey isn't that a valid HTML? It could be <name attribute>

Are there any better ways to check for "invalid" HTML where "invalid" is defined as HTML content with < followed some characters that is not a valid HTML Element as per specification?

Initially I thought I could check for <name followed by a (Space) but HTML Attributes have syntax as <name attribute and closed by > at the end.

score 8 · Accepted Answer · edited Apr 13 '17 at 12:42

If all you want to do is confirm that the input string consists of a sequence of <...> elements:

Split the string on the < character using your favourite string splitter
The first element must be blank
Subsequent elements must contain exactly one > character

The input string is valid if every element passes the tests above.

Example

String splitter

I am using the SQL#.String_Split4k streaming table-valued function from the free edition of the SQLCLR SQLsharp library by Solomon Rutzky.

If the input string might be longer than 4000 characters, the (less efficient) SQL#.String_Split function would be used instead (it handles strings up to 2GB).

Any efficient string splitter (T-SQL or otherwise) that can return empty elements correctly with a sequence number will work.

The example below shows the sort of output SQL#.String_Split4k returns.

The first parameter is the string to split. The second parameter is the delimiter. The third parameter (SplitOption) is set to 1, which means Keep Empty Elements.

SELECT
    SS.SplitNum,
    SS.SplitVal
FROM SQL#.String_Split4k(N'<a><b><c>', N'<', 1) AS SS;

Output:

╔══════════╦══════════╗
║ SplitNum ║ SplitVal ║
╠══════════╬══════════╣
║        1 ║          ║
║        2 ║ a>       ║
║        3 ║ b>       ║
║        4 ║ c>       ║
╚══════════╩══════════╝

Table and test data

CREATE TABLE #T
(
    row_id integer IDENTITY PRIMARY KEY,
    string varchar(50) NOT NULL
);

INSERT #T 
    (string)
VALUES 
    ('<html>Hello World</html>'),
    ('<html><Hello World</html>'),
    ('<html><Hello World></html>');

Solution

SELECT
    T.row_id,
    string = (SELECT TOP (1) T2.string FROM #T AS T2 WHERE T2.row_id = T.row_id),
    is_valid =
        MIN
        (
            CASE
                -- First element must be blank
                WHEN SS.SplitNum = 1 AND SS.SplitVal = N'' THEN 1
                WHEN SS.SplitNum = 1 THEN 0
                -- Other elements must contain exactly one >
                WHEN SS.SplitVal NOT LIKE N'%>%' THEN 0
                WHEN SS.SplitVal LIKE N'%>%>%' THEN 0
                -- Otherwise valid
                ELSE 1
            END
        )
FROM #T AS T
CROSS APPLY SQL#.String_Split4k(T.string, '<', 1) AS SS
GROUP BY T.row_id
ORDER BY T.row_id;

Output

╔════════╦════════════════════════════╦══════════╗
║ row_id ║           string           ║ is_valid ║
╠════════╬════════════════════════════╬══════════╣
║      1 ║ <html>Hello World</html>   ║        1 ║
║      2 ║ <html><Hello World</html>  ║        0 ║
║      3 ║ <html><Hello World></html> ║        1 ║
╚════════╩════════════════════════════╩══════════╝