problem in searching data from sql

[chetuashish]

I want to search data from a table where i want to seach according to five fields
the problem is that user can fill any field in any one of them and make other fileds blank,Then seach will done according the fiels supplied for seach.

[iburyak]

This is select statement I wrote for someone else.

Hope it helps

[PHP]select *
from TestTable
where column1 like case when isnull(@Variable1,'') = '' then '%' else @Variable1 end
and column2 like case when isnull(@Variable2,'') = '' then '%' else @Variable2 end
and ..... [/PHP]

You can go here to view discussion

http://www.thescripts.com/forum/thread623381.html

[CoolRiyaz]

Hi

Try this:

SELECT ColumnList FROM YourTable
WHERE COALESCE(Field1, Field2, Field3, Field4, Field5) = 'Search Criteria'
 

[iburyak]

Hi

Try this:

SELECT ColumnList FROM YourTable
WHERE COALESCE(Field1, Field2, Field3, Field4, Field5) = 'Search Criteria'
 

I don't think that COALESCE is correct approach.
Coalesce is searching for first not null field nothing to do with searching a value.