CHARINDEX actually works with text data type, but…

We had a table which was used to store error logs in a text column. From time to time I needed to extract some “error” messages out and I always resorted to charindex. Most of the time it worked just fine but sometimes I would get an empty result set back. As this behaviour wasn’t any show stopper so I never paid too much attention to it until one day I decided to get to the bottom of this seemingly random behaviour.

As usual, when I am uncertain about a system function, I would go to Books Online and read the definition, repeatedly at times. After scrolling down to the remarks section, a close inspection revealed something startling:

CHARINDEX cannot be used with text, ntext, and image data types.

This is simply not true! I know because I have been using charindex with text for months if not years. I am speculating that the author actually meant:

CHARINDEX should not be used with text, ntext, and image data types.

Now, I am going to show you with a simple demo that charindex CAN be used with text. However, there is a catch.

Let’s start with the script below:

-- Create a temp table for demo purpose.
declare @charindex_test table
( 
    text_data       text 
    , varchar_data  varchar(max) 
)

/*
Pretend that we have a long string with the
word error in there. Note the position of the
characters. 
 
Pos: 7995 7996 7997 7998 7999 8000
Char:   p    e    r    r    o    r
*/
insert @charindex_test (varchar_data) 
values (replicate(cast('p' as varchar(max))
        , 8000 - len('error')) + 'error') 

/*
Pretend that we have a long string with the
word error in there. Note the position of the
characters. 
 
Pos: 7996 7997 7998 7999 8000 8001
Char:   p    e    r    r    o    r
*/
insert @charindex_test (varchar_data) 
values (replicate(cast('p' as varchar(max))
        , 8001 - len('error')) + 'error')

-- Copy the content to a text column
update @charindex_test 
set text_data = varchar_data

-- Demonstrate the effect of using charindex with text
select 
    text_data 
    , varchar_data
    , datalength(text_data)            as 'text_data_bytes' 
    , charindex('error', text_data)    as 'text_data_pos' 
    , datalength(varchar_data)         as 'varchar_data_bytes' 
    , charindex('error', varchar_data) as 'varchar_data_pos' 
from @charindex_test

select *
from @charindex_test
where text_data like '%error%'

By running the entire script, we’ll get the output like the display below:

image

From this simple demo, we can make the following observations:

  • CHARINDEX can be used with text. However, there is an 8000 character limit.
  • LIKE on the other hand does not suffer from the 8000 characters limitation.
  • If you have to rely on CHARINDEX, then cast text as varchar(max) first.
  • You cannot blindly trust the official documentation. Always test and see with your own eyes.