I have a column, and I want to make a SP to search for rows that have
this column either starting with or ending with a given string, what is
the fastest way of doing this?

'starting with' of course is easy i can do
col LIKE @find + '%'
but if i do
col LIKE '%' + @find
for 'ending with', i believe that it will not use the index.

Does anyone have any thoughts on what to do?

Cheers
Greg

Try this:

DECLARE @searchstr VARCHAR(10)
DECLARE @expression VARCHAR(50)
DECLARE @vlength INT

SET @searchstr = 'ca'
SET @vlength = LEN(@searchstr)
SET @expression = 'cabbbbbcccccca'

IF LEFT(@expression,@vlength) = @searchstr AND
RIGHT(@expression,@vlength) = @searchstr
PRINT 'Its a match'
ELSE
PRINT 'Try again'

For pure speed, I would create another column using the Reverse
function. And place an index on this "colB".

e.g.
Update T
Set colB= reverse(colA)

The "end with" query would be:
colB like reverse(@find) +'%'