Menu
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.
491
By default, a column can hold NULL values. The NOT NULL constraint enforces a column to NOT accept NULL values. This enforces a field to always contain a value, which means that you cannot insert a new record, or update a record without adding a value to this field.
NULL means the absence of a value, meaning that LIKE and NOT LIKE should actually evaluate to FALSE and TRUE, respectively, when dealing with NULL values, and not the nonsensical NULL. NULL in SQL is supposed to mean "unknown" rather than "no value".
The SQL LIKE Operator The LIKE operator is used in a WHERE clause to search for a specified pattern in a column. There are two wildcards often used in conjunction with the LIKE operator: The percent sign (%) represents zero, one, or multiple characters. The underscore sign (_) represents one, single character.
COUNT(expression) does not count NULL values.
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>' If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
