LIKE operator and ESCAPE characters (how weird does it get?)

If you tried to post a comment on my previous post, you may have noticed an impressive error message. Sorry about that. It was the bug I was trying to fix. And I did, sort of. It shouldn’t get any weirder than this:

I noticed that some of my entries still suffered from the “Empty update, no affected rows”-syndrome, just some. I thought about other offending characters, such as the [ character that caused all the previous pain. But I couldn’t get it pinpointed. As it turned out, this was my problem, and you can reproduce it easily. Hold on, we’ll work to the Area52 bit.

First take any SQL Server database (say MyFunDB) that you can play around with. Then fire up SQL Query Analyzer (isqlw.exe) and run this bit of SQL.

CREATE TABLE WeirdStuff(Body ntext)
INSERT INTO WeirdStuff(Body) VALUES (‘[1]234567890’)
SELECT COUNT(*) FROM WeirdStuff WHERE Body LIKE (‘[1]234567890’)

Run it and it should return zero as the row count. That’s because of the [ character. Replacing the last line with:

SELECT COUNT(*) FROM WeirdStuff WHERE Body LIKE (‘[[]1]234567890’)

will return 1, because we have escaped the escape character. We’re approaching the freaky things now. Make both the inserted value and the LIKE value longer and longer, by copy-pasting sets of characters (I used 1234567890, so I had an easy count). Once you pass the magic length of 4000 for the LIKE value, it BREAKS! Zero rows after that. 4000 characters, returns 1, 4001 characters returns 0. Go figure. Remove the [ chars from both values and it works again. So, it’s only broken with escape characters and a length above 4000.

That is the reason for ‘my’ bug. Not really my bug, but my application suffers from it anyway. So don’t try for too long to post any comments, until I have this thing worked out. I do not seem to have passed the magic boundary, so comments should work.

This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s