p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   SQL Language (http://p2p.wrox.com/forumdisplay.php?f=100)
-   -   WHERE Clause, LIKE, and Nulls (http://p2p.wrox.com/showthread.php?t=58422)

jurio May 27th, 2007 04:49 AM

WHERE Clause, LIKE, and Nulls
 
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


All times are GMT -4. The time now is 01:06 PM.

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.