How about performance between PATINDEX and CHARINDEX?

Question

In all cases, I use CHARINDEXthe stored procedure to check the type of the variable NVARCHAR(MAX), but today I found that it CHARINDEXhas a limit of 8000 bytes!

I find this SQL article CHARINDEX () has data size limits .

So, I replace CHARINDEXwith PATINDEX, but I do not know the performance between PATINDEXand CHARINDEX.

Answer 1

CHARINDEX has a limit of 8000 for the string you are looking for. Not for the string you are looking for.

, , , 8000/4000. , . 2007 , SQL Server 2005 2000, SQL Server 2005 BOL 8000, , .

SQL Server 2008 BOL:

CHARINDEX (1, 2 [, start_location])

expression1 , . 1 8000 .

expression2 .

. PATINDEX 8000 SQL 2008 2005.

, CHARINDEX, , ,

Answer 2

CHARIndex 8000 ( ) , , - ntext. - varchar (max) nvarchar (max), 8000 ; . ( .) CAST: CHARINDEX(searchterm, CAST(columnname as nvarchar(max))