The code fails because the first character in the Pattern sequence is NCHAR(0). Any usage of the string will recognise this as the end of the string, truncating the string at this point. The solution is to start at NCHAR(1) instead.

😎

For demonstration, run first this code

DECLARE @Pattern NVARCHAR(MAX)

DECLARE @sSQL NVARCHAR(MAX)

SET @Pattern = '%['

SET @Pattern = @Pattern + NCHAR(0) + NCHAR(1) + NCHAR(2) + NCHAR(3) + NCHAR(4) + NCHAR (5) + NCHAR(6) + NCHAR(7) + NCHAR(8) + NCHAR(11) + NCHAR(12) + NCHAR(14) + NCHAR(15) + NCHAR(16) + NCHAR(17) + NCHAR(18) + NCHAR(19) + NCHAR(20) + NCHAR(21) + NCHAR(22) + NCHAR(23) + NCHAR(24) + NCHAR(25) + NCHAR(26) + NCHAR(27) + NCHAR(28) + NCHAR(29) + NCHAR(30) + NCHAR(31)

SELECT @Pattern

Return value

%[

And then this

DECLARE @Pattern NVARCHAR(MAX)

DECLARE @sSQL NVARCHAR(MAX)

SET @Pattern = '%['

SET @Pattern = @Pattern + NCHAR(0) + NCHAR(1) + NCHAR(2) + NCHAR(3) + NCHAR(4) + NCHAR (5) + NCHAR(6) + NCHAR(7) + NCHAR(8) + NCHAR(11) + NCHAR(12) + NCHAR(14) + NCHAR(15) + NCHAR(16) + NCHAR(17) + NCHAR(18) + NCHAR(19) + NCHAR(20) + NCHAR(21) + NCHAR(22) + NCHAR(23) + NCHAR(24) + NCHAR(25) + NCHAR(26) + NCHAR(27) + NCHAR(28) + NCHAR(29) + NCHAR(30) + NCHAR(31)

SELECT CONVERT(VARBINARY(MAX),@Pattern,3)

Return value

0x25005B000000010002000300040005000600070008000B000C000E000F0010001100120013001400150016001700180019001A001B001C001D001E001F00

This is probably way too obvious for one to notice at the first glance;-)