February 10, 2003 at 12:37 pm
Hi, all. I am getting weird results with CHARINDEX function in long text fields. In a SQL Server 2000 database, there is a text datatype column (not varchar - literally "text" datatype) that stores simple text of documents. It is full-text indexed.
CHARINDEX successfully returns string positions for strings in the beginning of this doctext column. But if the string targeted by CHARINDEX appears toward the end of the text in the field, the function returns 0. It's almost like after 8 kb of data (the limit for varchar), CHARINDEX does not work. This is not a case sensitivity issue either. We're talking the exact text that is returned by SELECT ... WHERE LIKE '%Metheny%' is not found by CHARINDEX if it is far down in the document text.
Has anyone else noticed this? I couldn't find anything about such limitations in either Books Online or through several Google searches.
February 11, 2003 at 5:41 am
Check BOL text datatype, "ntext, text and image" the list of usable functions on theses data types does not include charindex
February 11, 2003 at 5:44 am
Use Patindex instead of charindex on text data columns
February 11, 2003 at 8:53 am
Thanks, AnzioBake. Books Online suggests a preference for patindex on text columns, but does not say charindex won't work (as was the case with SQL 7). But I had tried using patindex, with the same problem. I have since found that others have noted the same problem I am having. CHARINDEX will not behave if the targeted string is after the first 8000 characters in a text column. Current workaround I'm looking at is to divide up the column in 8000 character chunks in my SELECT statements. Hope that will work.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply