Charindex & Patindex: does not handle nvarchar(max) or varchar(max) correctly generates error msg 8152 when used on expression 1 - by DB007

Status : 

  Fixed<br /><br />
		This item has been fixed in the current or upcoming version of this product.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.


2
0
Sign in
to vote
ID 537610 Comments
Status Closed Workarounds
Type Bug Repros 1
Opened 3/1/2010 5:47:03 AM
Access Restriction Public

Description

BOL States: 
CHARINDEX ( expression1 ,expression2 [ , start_location ] ) 
If either expression1 or expression2 is of a Unicode data type (nvarchar or nchar) and the other is not, the other is converted to a Unicode data type. CHARINDEX cannot be used with text, ntext, and image data types.
--> So what about nvarchar(max) or varchar(max) as the input on: expression 1 & 2 ??

We have some odd behaviour between expression 1 & 2 on both charindex and patindex:
(They are not treated the same)

The following has been tested on SQL 2005 : SP3 (Affects both X32 & 64-bit editions)
Try the following:

-- The following will return error msg: 8152
-- Which is search for string ABCDEF (replicated 40000 times)
-- and try to find it in string : CDEF
-- Should return 0.  And no error msg.
SELECT 
	PATINDEX (
	(REPLICATE(CAST('ABCDEF' AS VARCHAR(MAX))
			,40000))
			, 'CDEF')


-- The following will return results of 3
-- Try to find string: %CDEF% in string
-- ABCDEF (replicated 40000 times)
-- Should return 3.  And no error msg.
SELECT 
	PATINDEX ( '%CDEF%',
	(REPLICATE(CAST('ABCDEF' AS VARCHAR(MAX))
			,40000))
	)
	
-- The following will return error msg: 8152
-- Which is search for string ABCDEF (replicated 40000 times)
-- and try to find it in string : CDEF
-- Should return 0.  And no error msg.
SELECT 
	CHARINDEX ( (REPLICATE(CAST('ABCDEF' AS VARCHAR(MAX))
			,4000)),'CDEF'
	)
	
	
-- The following will return results of 3
-- Try to find string: %CDEF% in string
-- ABCDEF (replicated 40000 times)
-- Should return 3.  And no error msg.
SELECT 
	CHARINDEX ( 'CDEF',
	(REPLICATE(CAST('ABCDEF' AS VARCHAR(MAX))
			,4000))
	)
	



Sign in to post a comment.
Posted by Microsoft on 6/14/2012 at 1:13 PM

I've updated both SQL Server 2008 R2 Books Online and SQL Server 2012 Books Online,
to add the following sentence to the description of the PATINDEX pattern parameter:

"pattern is limited to 8000 characters."

Thanks.

Regards.
Margi Showman,
SQL Server Documentation Team [MSFT]


Posted by Microsoft on 6/16/2010 at 1:10 PM
Hi,

Error message 8152 is expected in this situation. It tells us the pattern would be truncated because in CHARINDEX and PATINDEX, the pattern length cannot exceed 8,000 bytes, unlike the searched string which can.

The documentation of
- CHARINDEX http://msdn.microsoft.com/en-us/library/ms186323.aspx
- LIKE http://msdn.microsoft.com/en-us/library/ms179859.aspx
explain this limitation in the size of the pattern.

Though the documentation of
- PATINDEX http://msdn.microsoft.com/en-us/library/ms188395.aspx
does not. I will follow up with the documentation team to add that clarification for PATINDEX.

Thank you for reporting this issue.
Miles Trochesset - Microsoft SQL Server
Posted by DB007 on 3/30/2010 at 5:13 AM
This also affects SQL 2008 SP1 CU10:
Error returned is:
Msg 8152, Level 16, State 10, Line 5
String or binary data would be truncated.

Microsoft SQL Server 2008 (SP1) - 10.0.2766.0 (X64) Feb 25 2010 12:51:37 Copyright (c) 1988-2008 Microsoft Corporation Developer Edition (64-bit) on Windows NT 5.2 <X64> (Build 3790: Service Pack 2)
Posted by DB007 on 3/8/2010 at 6:07 AM
I should have a sql 2008 box up soon, so should be able to validate if this also occurs on sql 2008.
Posted by Microsoft on 3/4/2010 at 1:50 PM
Hi,

Thanks for filing this. It's not immediately clear to me what's going on, but our dev team will take a look and we'll get back to you.

Cheers,
-Isaac
Posted by DB007 on 3/2/2010 at 2:19 AM
ignore the comment below. It is working correctly with long-fields in expression 2. It only affects expression 1.
(code should have read:
SELECT
    PATINDEX ( '%SEXY%',
    (REPLICATE(CAST('ABCDEF' AS VARCHAR(MAX))
            ,40000)+'SEXY')
    )
    -- Forgot to remove the % % either side of search string as its charindex
SELECT
    CHARINDEX ( 'SEXY',
    (REPLICATE(CAST('ABCDEF' AS VARCHAR(MAX))
            ,40000)+'SEXY')
    )

)
Posted by DB007 on 3/2/2010 at 2:17 AM
I also have found an additional bug with charindex, not handling searchs > 8000 chars long:

-- Input: Expected Output: Finds string at position 240001
-- Actual Output Finds string at position 24001
SELECT
    PATINDEX ( '%SEXY%',
    (REPLICATE(CAST('ABCDEF' AS VARCHAR(MAX))
            ,40000)+'SEXY')
    )


-- Input: Expected Output: Finds string at position 240001
-- Actual Output = 0
SELECT
    CHARINDEX ( '%SEXY%',
    (REPLICATE(CAST('ABCDEF' AS VARCHAR(MAX))
            ,40000)+'SEXY')
    )

We do need to get these fixed ASAP [This is a severe bug, and affects searching for text-strings in a field in a major way]