like '%' does not accept NULL value

Question

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.

I hope you are using parameterized queries, as your code may very well be open to SQL Injection. See [boby tables](http://xkcd.com/327/) — Oded, Oct 13 '10 at 13:54
Where does the parameter come from? (@Oded: I guess that's what `''` was supposed to mean…) — Tomalak, Oct 13 '10 at 13:56

score 68 · Accepted Answer · answered Oct 13 '10 at 13:53

68

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

answered Oct 13 '10 at 13:53

Andomar

232,371
49
380
404

What about the case when an index is defined on [table].[column]? – bjnr Feb 14 '14 at 06:59
1

On oracle (at least the version I'm using, 10.2), coalesce doesn't work with an empty string. I have to use `COALESCE([table].[column],' ')` to match fields. – Teleporting Goat Sep 11 '19 at 13:23

Pedro Rodrigues · Answer 2 · 2019-09-25T12:28:10.487

8

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

Works like a charm

edited Sep 25 '19 at 12:28

answered Jul 27 '15 at 10:03

Pedro Rodrigues

2,520
2
27
26

score 3 · Answer 3 · answered Oct 13 '10 at 13:53

3

I think this might work:

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

answered Oct 13 '10 at 13:53

bernhardrusch

11,670
12
48
59

score 3 · Answer 4 · answered Oct 13 '10 at 13:54

3

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.

answered Oct 13 '10 at 13:54

Matt Gibson

37,886
9
99
128

Shawson · Answer 5 · 2022-10-27T08:13:50.937

2

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

edited Oct 27 '22 at 08:13

answered Oct 13 '10 at 13:57

Shawson

1,858
2
24
38

score 1 · Answer 6 · edited May 23 '17 at 11:54

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

This answer is just a lump of code, it seems very similar to another answer. Please explain why this is a good answer, how the code works and how it differs from the other answers. — AdrianHHH, Oct 06 '15 at 12:37

Tim · Answer 7 · 2010-10-13T14:05:00.827

1

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

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

edited Oct 13 '10 at 14:05

answered Oct 13 '10 at 13:56

Tim

2,831
1
25
37

Surprisingly, `NULL` != `NULL` , you need to use the special operator `IS NULL` – Piskvor left the building Oct 13 '10 at 13:58
Isn't that the same as `select * from [table]`? – Caramiriel Oct 06 '15 at 14:20
Yes, but usually you'll have more where fields, and the % could be a parameter supplied by PHP, for example – Berry M. Oct 02 '16 at 10:29

score 0 · Answer 8 · edited Dec 16 '15 at 19:07

0

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

edited Dec 16 '15 at 19:07

Slava Vedenin

58,326
13
40
59

answered Dec 16 '15 at 18:14

Neftali Rios

1
1

score 0 · Answer 9 · edited Jul 15 '16 at 20:02

0

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.

edited Jul 15 '16 at 20:02

trincot

317,000
35
244
286

answered Jul 15 '16 at 19:38

nathie

1

`coalesce` works on Oracle. Your code is equivalent to `where coalesce(table1.field, ' ') like '%'` – trincot Jul 15 '16 at 20:01

score 0 · Answer 10 · answered Aug 14 '19 at 20:47

0

For SQLITE

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

answered Aug 14 '19 at 20:47

ndw

513
6
14

like '%' does not accept NULL value

10 Answers10

Linked