Question
You have a SQL database in Microsoft Fabric that contains a nvarchar(max) column named MessageText. An ID is always contained within the first paragraph of MessageText.
You need to write a Transact-SQL query that uses REGEXP_SUBSTR to extract the ID from MessageText.
What should you include in the query?
- A. Apply STRING_ESCAPE(MessageText, 'json') before calling REGEXP_SUBSTR.
- B. Cast MessageText to nvarchar(4000) before calling REGEXP_SUBSTR. Correct
- C. Add a COLLATE Latin1_General_CS_AS clause to MessageText before calling REGEXP_SUBSTR.
- D. Run TRY_CONVERT(varchar(max), MessageText) before calling REGEXP_SUBSTR.
Explanation
The REGEXP_* functions accept nvarchar(max) only up to 2 MB, so a very long value can make the call fail. Because the ID is always in the first paragraph, casting MessageText to nvarchar(4000) keeps the part that holds the ID and removes the large object limit from the call. STRING_ESCAPE changes the text by adding escape characters and solves nothing here. Regular expressions do not honor collation rules, so a COLLATE clause has no useful effect, and converting to varchar(max) is still a large object type and can lose Unicode characters.
