Microsoft documents REGEXP_SUBSTR for Transact-SQL with the string_expression parameter as supporting character string types char, nchar, varchar, and nvarchar. For the regex functions, support for LOB types such as varchar(max) and nvarchar(max) is specifically called out for REGEXP_LIKE , REGEXP_COUNT , and REGEXP_INSTR up to 2 MB, but that support note is not listed for REGEXP_SUBSTR in the surfaced documentation. In exam terms, the safe and expected approach is to cast the nvarchar(max) column to nvarchar(4000) before calling REGEXP_SUBSTR.

This also fits the scenario detail that the ID is always contained within the first paragraph of MessageText. Since the needed value is near the start of the text, narrowing the input to a non-LOB string type such as nvarchar(4000) is sufficient and avoids incompatibility concerns with nvarchar(max).

The other options are not appropriate:

A STRING_ESCAPE(..., ' json ' ) is for JSON escaping, not regex extraction.

C adding a case-sensitive collation changes comparison behavior, but it is not the required fix for REGEXP_SUBSTR on nvarchar(max).

D TRY_CONVERT(varchar(max), ...) still leaves a MAX type and also risks unnecessary Unicode loss.