tags:

views:

69

answers:

5

I have query that is build from user's inputs ( passed via htm form). It looks like (simple example):

Select * From [table] Where [table].[column] like '<parameter>'

This parameter may be optional so if user left corresponding input field empty I pass %. It worked fine until I encountered NULL values. I understand that '%' match symbols not null, but i would like to consider NULL as empty string in this case.

What should I do? Change query (how?) or pass another symbol(s) when user left empty input?

Thanks.

PS. It's real problem from existing system and i know it's far from optimal solution, but i have to deal with it.

+9  A: 

You can use coalesce to treat null like an empty string:

where COALESCE([table].[column],'') like '<parameter>'

On SQL Server, you can also use IsNull:

where IsNull([table].[column],'') like '<parameter>'
Andomar
+1  A: 

I think this might work:

Select * From [table] Where [table].[column] is null or [table].[column] like '<parameter>'
bernhardrusch
A: 

Well, how about

SELECT 
  * 
FROM 
  [table] 
WHERE
  ([table].[column] like <parameter>) OR 
  (<parameter> = '%')

...so that when you pass '%', you get all the rows back, otherwise it works like you have it at the moment.

Matt Gibson
A: 

Make two statements! If the user passed no parameter user:

Select * From [table] Where [table].[column] like '%' or [table].[column] is null;
Tim Krüger
Surprisingly, `NULL` != `NULL` , you need to use the special operator `IS NULL`
Piskvor
Ups! Sorry! I corrected it.
Tim Krüger
A: 

how about..

Select * From [table] Where ISNULL([table].[column], '') like '<parameter>'

So this will take your actual column value, or if thats null an empty string and compare it aganist your parameter, assuming your using ms sql server..

Shawson