WildCard search in SQL Server 2005?

[bhavna_816]

I have a table which have person's name as Prefix, firstname, lastname, address as three
different columns.
I want to get all records when fire a query like

SELECT * from Persons where name= 'abcd'

I want such query which can search in all three columns, it means abcd can be in
Prefix, firstname or lastname column.

Actually I have a page in which there are some text boxes which accepts some inputs and
based on that inputs displays matching records in GridView.

Suppose someone entered 'Ind', so my query should find records in all three columns of name.

And if someone entered record in first text box say name and nothing is entered in address text box, then
the records matching with that name with all addresses should get displayed.

How can I do that?

Thanks in advance,

[bhavna_816]

I have joined three columns as

SELECT (Prefix+' '+firstname+' '+lastname) from Persons

[campkev]

select * from persons where (prefix like '%'+@input+'%'
or 
firstname like '%'+@input+'%'
or
lastname like '%'+@input+'%')
and address like '%'+@addressinput+'%'

[gunso4050]

Thank you very much this has been a very useful post as i was having exactly the same problems.

Thanks again

Matt :) :)