like '%' does not accept NULL value

问题:

I have query that is build from user's inputs ( passed via html 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.

回答1:

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

回答2:

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

Works like a charm

回答3:

I think this might work:

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

回答4:

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.

回答5:

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

回答6:

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

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

回答7:

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

回答8:

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

回答9:

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.

回答10:

For SQLITE

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