Text overflow when using nvarchar(max)?

Question

I've run into a strange situation that seems to involve long text overflowing. I'm using fn_pcre_replace (from the xp_pcre extended stored procedure) on an nvarchar(max) column, and when I replace with more characters than were there originally, it returns NULL if the original string was over 8000 characters long.

For example, this

Select master.dbo.fn_pcre_replace(Overview,'a', 'X')
from ProjectContent

works as expected, but this

Select master.dbo.fn_pcre_replace(Overview,'a', 'XXX')
from ProjectContent

returns NULL in most cases (apparently, wherever Len(Overview)>8000).

Is this a limitation of xp_pcre, or is there something I can do in my SQL to make this work?

Answer 1

This is a limitation of xp_pcre. Looking at the source:

 switch (bType)
 {
 case SRVBIGCHAR:
 case SRVBIGVARCHAR:
  break;
 default:
  throw XpException(
   StringBuilder()
   << "Invalid data type on parameter "
   << paramNum
   << " (should be CHAR or VARCHAR)."
   );
 }

I can conclude these two values (from <srv.h>) permit up to a maximum of 8000 characters. SRVBIGVARCHAR is

Variable-length character data type, length 0 to 8000 bytes.

You would need to update the source and recompile with support for SRVTEXT or SRVVARCHAR so it is not a limiting factor when using external procedures.