I assume you mean that you want a case-sensitive search on a
case-insensitive server. Obviously the ideal is to have a sort order that
meets your requirements. You can change the sort-order in 7.0 but only at
the server-level.

This should do it under any sort order:

SELECT col1
FROM Table_X
WHERE col1 LIKE '% AVE %'
AND CAST(SUBSTRING(col1,PATINDEX('% AVE %',col1),5) AS VARBINARY)
= CAST(' AVE ' AS VARBINARY)

--
David Portas
SQL Server MVP
--