Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Database > SQL Language
| Search | Today's Posts | Mark Forums Read
SQL Language SQL Language discussions not specific to a particular RDBMS program or vendor.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Language section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old May 27th, 2007, 04:49 AM
Registered User
 
Join Date: May 2007
Location: , , .
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default 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.



  #2 (permalink)  
Old May 27th, 2007, 09:49 AM
Imar's Avatar
Wrox Author
Points: 70,322, Level: 100
Points: 70,322, Level: 100 Points: 70,322, Level: 100 Points: 70,322, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 Posts
Default

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.
  #3 (permalink)  
Old May 27th, 2007, 01:12 PM
Registered User
 
Join Date: May 2007
Location: , , .
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.





  #4 (permalink)  
Old May 27th, 2007, 04:03 PM
Imar's Avatar
Wrox Author
Points: 70,322, Level: 100
Points: 70,322, Level: 100 Points: 70,322, Level: 100 Points: 70,322, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 Posts
Default

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.
  #5 (permalink)  
Old May 27th, 2007, 10:30 PM
Registered User
 
Join Date: May 2007
Location: , , .
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.

  #6 (permalink)  
Old May 28th, 2007, 02:53 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

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


Similar Threads
Thread Thread Starter Forum Replies Last Post
IN and NULLs joxa83 SQL Server 2005 8 September 29th, 2008 03:03 AM
re arrrange nulls in order by clause mat41 SQL Language 13 August 7th, 2007 08:10 PM
Find Nulls jemacc SQL Server 2000 1 September 15th, 2004 01:28 PM
looking for nulls Warbird C# 4 July 1st, 2004 07:50 AM
Handling Nulls shahchi1 ADO.NET 4 June 24th, 2004 11:29 AM





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