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:
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.