like '%' does not accept NULL value

I have query that is build from user's inputs ( passed via h开发者_如何学运维tml 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.

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>'

isnull([table].[column], '') like '%'

Works like a charm

I think this might work:

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

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.

How about..

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

So this will take your actual column value, or if that's null an empty string and compare it against your parameter, assuming you're using MS SQL server..

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

Select * From [table] Where [table].[column] like '%' or [table].[column] is null;

Based On Index in Where Clause Issue With This Approach

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

Is :

If you have used an Index on your [column], because of the COALESCE function,SQL Server cant use your index,it means that you've wasted your index

AND

Issue With This Approach

Where [table].[column] like '%' or [table].[column] is null

Is :

If the [table].[column] is null then the code will be like this :

Where null like '%' or [table].[column] is null

and regardless of second part of Where clause ([table].[column] is null) the result of evaluation will be UNKNOWN and the SQL Server filter that record out.

NULL OR True = UNKNOWN

NULL OR False = UNKNOWN

So this is the optimized and null included approach :

Select * From [table] 
 WHERE 
      CASE 
          WHEN [table].[column] IS NULL THEN 1 
          WHEN [table].[column] like '<parameter>' THEN 1 
          ELSE 0 
      END   =  1

Good day, use this solution, I think it's a mixture of solutions:

@parameter nvarchar (30)

if @parameter = ''
      Begin
          Set @parameter = '%'
      End

select * from [table] as t where ISNULL (t. [column], '') like '%' + @parameter + '%'

If you just want to start with the parameter, removes the first '%' and the plus sign

I hope you find it useful

I wanted something similar, to actually be able to find '%' (all) including null or a specific value when input.

For Oracle isnull does not work and in my case COALESCE neither.

I Used this option in the where clause:

where decode(table1.field1,null,' ',table1.field1) like '%'

Hope it works for others.

For SQLITE

SELECT * FROM [table] WHERE IFNULL([table].[column],'') like '<parameter>'