At all times I use CHARINDEX in stored procedure to check NVARCHAR(MAX) type of variable, but today I find that the CHARINDEX has an 8,000 byte limit!
I find this article SQL CHARINDEX() Has Data Size Limitations.
So I replace CHARINDEX with PATINDEX, but I do not know the performance between PATINDEX and CHARINDEX.
CHARINDEX has limit of 8000 for the string you are looking for. Not for the string you are searching.
That article is probably wrong unless you want to look for strings > 8000/4000 length. However, it also uses text datatype which is deprecated. It's dated 2007, so it would be SQL Server 2005 or 2000 he used but SQL Server 2005 BOL does not mention 8000 at all and it's not something I've tried.
From the SQL Server 2008 BOL:
CHARINDEX ( expression1 ,expression2 [ , start_location ] )
expression1 Is a character expression that contains the sequence to be found. expression1 is limited to 8000 characters.
expression2 Is a character expression to be searched.
Note: PATINDEX does not mention 8000 limit for SQL 2008 or 2005.
Finally, I'd use CHARINDEX because I think it's more intuitive for straightforward searches if you don't need pattern matching and it supports long strings
CHARIndex does have 8000-byte (not character) limit for the string you are searching, IF the datatype is text or ntext. If the datatype is varchar(max) or nvarchar(max), the 8000-byte limit is not in effect; it searches the entire string. (I was just bit by this.) I resolved this just using CAST: CHARINDEX(searchterm, CAST(columnname as nvarchar(max))