CHARINDEX returns zero in TEXT column

I'm running into an issue where CHARINDEX on a text datatype column returns
0
if the expression I'm searching for exists at a position greater than 8000.
For example:
use pubs;
select charindex('New Moon Books',pr_info,8000)
from pub_info
returns zero, even though I know 'New Moon Books' exists past character
8000. Is this a known issue, and is there a workaround? It's causing my
search and replace procedure (using the UPDATETEXT function) to fail, i.e.
LIKE '%searchfor%'
is true but
CHARINDEX('searchfor',textColumn)
is zero.
Any help is appreciated.Did you try PATINDEX?
"Alan Smithee" <AlanSmithee@.discussions.microsoft.com> wrote in message
news:F8D5F76C-9114-47F1-A584-96035BFA38B6@.microsoft.com...
> I'm running into an issue where CHARINDEX on a text datatype column
> returns 0
> if the expression I'm searching for exists at a position greater than
> 8000.
> For example:
> use pubs;
> select charindex('New Moon Books',pr_info,8000)
> from pub_info
> returns zero, even though I know 'New Moon Books' exists past character
> 8000. Is this a known issue, and is there a workaround? It's causing my
> search and replace procedure (using the UPDATETEXT function) to fail, i.e.
> LIKE '%searchfor%'
> is true but
> CHARINDEX('searchfor',textColumn)
> is zero.
> Any help is appreciated.|||CHARINDEX will not work for strings larger than 8000. To work with TEXT
fields larger than this size, you will need to use the TEXT functions in SQL
Server 2000 like READTEXT, WRITETEXT etc.
--
HTH,
SriSamp
Email: srisamp@.gmail.com
Blog: http://blogs.sqlxml.org/srinivassampath
URL: http://www32.brinkster.com/srisamp
"Alan Smithee" <AlanSmithee@.discussions.microsoft.com> wrote in message
news:F8D5F76C-9114-47F1-A584-96035BFA38B6@.microsoft.com...
> I'm running into an issue where CHARINDEX on a text datatype column
> returns 0
> if the expression I'm searching for exists at a position greater than
> 8000.
> For example:
> use pubs;
> select charindex('New Moon Books',pr_info,8000)
> from pub_info
> returns zero, even though I know 'New Moon Books' exists past character
> 8000. Is this a known issue, and is there a workaround? It's causing my
> search and replace procedure (using the UPDATETEXT function) to fail, i.e.
> LIKE '%searchfor%'
> is true but
> CHARINDEX('searchfor',textColumn)
> is zero.
> Any help is appreciated.|||charindex won't work with text datatype. Use patindex|||Thanks Aaron, you of course are correct, PATINDEX works! (I was sure I had
tried that before, but I think I left out the wildcard character).
Anyway, much thanks (and thanks to Omnibuzz too!)
A.S.
"Aaron Bertrand [SQL Server MVP]" wrote:

> Did you try PATINDEX?
>
> "Alan Smithee" <AlanSmithee@.discussions.microsoft.com> wrote in message
> news:F8D5F76C-9114-47F1-A584-96035BFA38B6@.microsoft.com...
>
>