locked
testing for null in sql code RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hello,
    .
    How do you test for a null value in an SQL select statement ?
    .
    I have a query in which I am listing a parts inventory.... I would like my query to substitute a zero value when ever a null value is encountered...
    .
    For example, my basic SQL statement would be:
    .

    SELECT part_name, part_quant, part_price 
      FROM tblTotals
     ORDER BY part_name

    .
    How would I get it to show a zero value for the quantity and the price if they were null?
    .
    Thank you in advance, any help is greatly appreciated and will be acknowledged !
    .
    Regards,
    .
    Bruce V. ..aka..OASys1

    Monday, March 5, 2012 12:59 AM

Answers

  • Question
    Sign in to vote
    1
    Sign in to vote

    Hi Bruce, you'll want to use the ISNULL() function for this replacement. 

    http://msdn.microsoft.com/en-us/library/ms184325.aspx

    SELECT part_name, ISNULL(part_quant,0), ISNULL(part_price,0) 
  FROM tblTotals
 ORDER BY part_name
    Thanks,
    Sam Lester (MSFT)

    This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you. This can be beneficial to other community members reading the thread.

    Monday, March 5, 2012 1:06 AM
  • Question
    Sign in to vote
    1
    Sign in to vote
    Check COALESCE() function in BOL

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    • Marked as answer by OASystems Monday, March 5, 2012 3:36 AM
    Monday, March 5, 2012 1:43 AM

All replies

  • Question
    Sign in to vote
    1
    Sign in to vote

    Hi Bruce, you'll want to use the ISNULL() function for this replacement. 

    http://msdn.microsoft.com/en-us/library/ms184325.aspx

    SELECT part_name, ISNULL(part_quant,0), ISNULL(part_price,0) 
  FROM tblTotals
 ORDER BY part_name
    Thanks,
    Sam Lester (MSFT)

    This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you. This can be beneficial to other community members reading the thread.

    Monday, March 5, 2012 1:06 AM
  • Question
    Sign in to vote
    1
    Sign in to vote
    SELECT part_name, isnull(part_quant,0), isnull(part_price,0) FROM tblTotals ORDER BY part_name Sri krishna
    • Proposed as answer by Naomi N Monday, March 5, 2012 1:43 AM
    • Marked as answer by OASystems Monday, March 5, 2012 3:38 AM
    • Unmarked as answer by OASystems Monday, March 5, 2012 3:38 AM
    Monday, March 5, 2012 1:06 AM
  • Question
    Sign in to vote
    1
    Sign in to vote
    Check COALESCE() function in BOL

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    • Marked as answer by OASystems Monday, March 5, 2012 3:36 AM
    Monday, March 5, 2012 1:43 AM
  • Question
    Sign in to vote
    1
    Sign in to vote
     

    Use

    isnull

    or

    case when isnull(part_name,’ ‘)=’ ‘ …

    statement to verify that

    Many Thanks & Best Regards, Hua Min

    Monday, March 5, 2012 3:22 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Thanks to all  that replied to my inquiry....

    Kind Regards,

    Bruce V. ...aka...OASys1

    Monday, March 5, 2012 3:35 AM