"Error converting data type nvarchar to real" using LIKE operator on CHAR column

View1 has a CHAR column of CharCol1. While using LIKE operator on this column, I receive below error.

Select * from View1 Where CharCol1 Like '%77%'

Msg 8114, Level 16, State 5, Line 1

Error converting data type nvarchar to real.

But all of below syntax works:

Select * from View1 Where CharCol1 like N'%77%'

Select * from View1 Where CharCol1 Like '77'

Select * from View1 Where CharCol1 = '77'

Why does SQL 2005 treat '%77%' as real? This used to be working in SQL 2000.

It works for me in SQL 2005. Are you sure your view's CharCol1 is really a character column, and hasn't been casted as real?|||

Actually the view was being used in SQL 2000 and after SQL 2005 upgrade, this error has started.

Last view used a view and that view also uses another view. There are three levels. But one of the views used in the first view is then joined in the last view which gives this error. Of course, this is not good programming but it was working in the previous version. Now, I have removed this join and carried the necessary columns from the first level thus I eliminated double joining of same view at the last level. This solved the problem.

Actually, the problem is that, I had tested this view and they were ok. But once I give a Where clause using a column from the double joined view, I get this error. Unfortunately, we encountered this error after upgrade and on the live system we had to fix it.

What I understand is that somehow in SQL 2005, we can get errors if the coding is not good and the views should be tested by giving "Where" clauses using all possible columns.

Hope this is clear.

Does anyone have comment on this?