Home Dashboard Directory Help
Search

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


Status: 

Closed
 as Fixed Help for as Fixed


2
0
Sign in
to vote
Type: Bug
ID: 537610
Opened: 3/1/2010 5:47:03 AM
Access Restriction: Public
0
Workaround(s)
view
1
User(s) can reproduce this bug

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))
    )
    



Details
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]
Sign in to post a workaround.