WHERE Clause, LIKE, and Nulls

jurio · May 27th, 2007, 04:49 AM

I'm trying to use the SQL WHERE Clause with some parameters so the user can search the database.

SELECT * FROM TABLE
WHERE COLUMN LIKE '%' + @Column + '%'

The problem is, this column has Null values for many of the rows.

If I supply the wildcard parameter ('%'), I'm only returned the rows which have a non-null value in that column.

I would like it to return rows which have a null value for the column as well, because I want this search feature to return the entire recordset if the user leaves the parameter associated controls blank.

Imar · May 27th, 2007, 09:49 AM

Depending on your requirements, you can check if Column or @Column is null:

WHERE COLUMN LIKE '%' + @Column + '%' OR @Column IS NULL

This example returns all rows when the actual search term (@Column) equals null.

Similarly, you can check the value in the column as well:

WHERE COLUMN LIKE '%' + @Column + '%' OR Column IS NULL

This returns all records where either a match is found with the like operator or where the actual column data is null.

Hope this helps,

Imar

---------------------------------------
Imar Spaanjaars
http://Imar.Spaanjaars.Com
Everyone is unique, except for me.
Author of ASP.NET 2.0 Instant Results and Beginning Dreamweaver MX / MX 2004
Want to be my colleague? Then check out this post.

jurio · May 27th, 2007, 01:12 PM

Thanks for the reply.

My issue with OR IS NULL approach is that if a parameter is supplied, such as "abc", then all rows which contain "abc" will be returned, along with all rows which contain null values.

Since this query is going to be used for user searches, if the user searches for a specific value, they wont want to see null rows in addition to their rows containing the value(s) they supply.

Imar · May 27th, 2007, 04:03 PM

Quote:

quote:Originally posted by jurio
I would like it to return rows which have a null value for the column as well

Quote:

quote:they wont want to see null rows in addition to their rows containing the value(s) they supply.

This seems to contradict, so I am confused right now.

Can you explain what it is that you want exactly?

Imar

---------------------------------------
Imar Spaanjaars
http://Imar.Spaanjaars.Com
Everyone is unique, except for me.
Author of ASP.NET 2.0 Instant Results and Beginning Dreamweaver MX / MX 2004
Want to be my colleague? Then check out this post.

jurio · May 27th, 2007, 10:30 PM

Sure,

because a wildcard parameter should show all rows, including null value rows.

and a non-wildcard, actual value passed to the parameter should not show null values.

happygv · May 28th, 2007, 02:53 AM

jurio,

So how is that you differentiate between Wildcard and Non-Wildcard values that are passed as parameters?

I think you should play with or without LIKE operator for getting around this.

Code:

-- checking for actual value of the parameter passed.
SELECT * FROM TABLE_NAME 
    WHERE ColName = @Parameter
-- IF the above query return rows then dont proceed to next query, 
else proceed further to get the pattern matching results

-- checking for PATTERN match of the parameter passed.
SELECT * FROM TABLE_NAME 
    WHERE ColName LIKE '%' + @Parameter + '%' OR ColName IS NULL

Cheers

_________________________
- Vijay G
Strive for Perfection