I have been trying to write a query that will allow me to search through text fields. This is a problem because SQL doesn't let me use any functions on TEXT datatypes.

When I was using access I did it like this:

where upper(searchtext) like ('%SEARCHSTRING%&#39

However UPPER doesn't work on text fields (I want the search to be case insensitive)

I tried this:
where patindex(searchtext, &#39;%SEARCHSTRING%&#39 <> 0

but that is not case-insensitive...
Help me SQL gurus, you are my only hope