Is there a reason that you mention subtracting the length of the final string from 8000 like so

ISNULL(NULLIF(CHARINDEX(@pDelimiter, @pString, s.N1), 0), 8000) - s.N1

and then in the final code...