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

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