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

A tale of SQL Server installation

I had to reinstall SQL Server 2000 on a box a few weeks ago. The mission sounded simple when I first started but quickly turned into an eventful drama. Below is a recount of the events I had experienced.

One afternoon, a user reported that he could no longer run reports against a database. He also claimed to have successfully extracted data earlier in the day. The symptom didn’t sound too complicated.

After having difficulty connecting to the SQL Server from SSMS, I went onto the box and discovered that the SQL Server service was stopped.

That’s strange but still no big deal. I clicked on the green start button in the service manager, once…twice…three times and the server was still not started. I sensed something was not right.

A trip to the event viewer revealed the problem:

17052: SQL Server evaluation period has expired.

Huh? All along I didn’t realise we were running an evaluation version. Now what? We had proper licences and a reinstallation would be the logical step but due to the time constraint, I didn’t have time to mark around, it was getting late and the user needed his data urgently.

One of my team mate suggested a dirty quick trick at this point: move the system clock back one day and pretend we were still within the evaluation period. You know what it actually worked. I thought I was smart but this guy was a real “problem solver”.

The system clock trick was nice but it was just a sticky tape solution, we really needed to install a proper version.

Came next day, I naturally selected the enterprise edition (since it was to replace the evaluation enterprise edition) and happily clicked on setup.exe.

Microsoft SQL server 2000 enterprise edition server component is not supported on this operating system, Only client component will be available for installation.

Oops, I later found out that neither enterprise nor standard edition was supported on a Windows XP box, so I was left with no choice but to settle on a developer edition. Fast forward and I clicked on the setup.exe again but this time I was presented with a different message:

A previous program installation created pending file operations on the installation machine. You must restart the computer before running setup.

Fine, so I restarted the box…three times and the message was still lingering around. Between the restarts, I had uninstalled SQL Server 2000 (and inadvertently lost all the DTS packages stored in msdb, luckily I kept a pretty recent backup of msdb, phew!)

In the end, I had to delete a registry entry following instructions from the KB article: SQL Server 2000 installation fails with “…previous program installation…” error message

Thirty minutes later, SQL Server 2000 was successfully installed. OK, time to restore the msdb database to recover all the DTS packages and jobs, etc. It didn’t take me long to locate the msdb backup and type in the command to restore it…

Msg 3168, Level 16, State 1, Line 1
The backup of the system database on device C:\Backup\msdb.bak cannot be restored because it was created by a different version of the server (134219767) than this server (134217922).

Now what?! After staring at the error message for a while, I remembered that the previous SQL Server was on SP4. It got to be it.

Click…click…click, SP4 was installed without any hiccups. Attempted the msdb restore again and thank god it worked this time. I had finally restored the server to the state it was two day ago. What a “fun” experience!