Like Comparison Problems

Hello im trying to run a simple query where i compare something simple like BEARING to a field in the database using this query.


Code:


SELECT table1.STK_NO, table1.STK_NO2
FROM table1
WHERE (LEN(CLEANSED_REF)>2)
AND ('BEARING' LIKE '%'+table1.CLEANSED_REF+'%')



Inside the table i can see the CLEANSED_REF field and its value is "000BEARINGBRNG000". When i run the query i get no records returned.

But if i run

Code:


SELECT table1.STK_NO, table1.STK_NO2
FROM table1
WHERE (LEN(CLEANSED_REF)>2)
AND (table1.CLEANSED_REF LIKE '%BEARING%')



The query returns the row i wanted. Can anyone tell me why this wont work the first way, but works the second?