Isnull behavior

  • Suppose I have 3 fields which are varchar

    Area_code

    Prefix_code

    Last4_code

    And suppose the Area_code field is null and the other two field contain 555 and 1212.

    The following

    select IsNull(Area_code+Prefix_code+Last4_code,'Invalid Phone') from clients

    will return 'Invalid Phone' when running in SQL 2000

    I'm wondering if the behavior is the same in later versions of SQL Server..

    Thanks

  • Yes. Combining string values with the '+' operator results in a NULL any time one of the values contain a NULL. This is expected behavior. Since NULL means unknown, adding it to an existing string value results in an unknown.

    John Rowan

    ======================================================
    ======================================================
    Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

  • That is what i wanted to hear! Thanks!

  • Reading between the lines a bit, it sounds like you might be willing to tolerate the areacode being null. You can still catch invalid phone numbers if you were to push the isnull down one more level. Something like:

    select IsNull( isnull(Area_code,'(000)')+Prefix_code+Last4_code,'Invalid Phone')

    from clients

    would only bounce this as invalid if either of the last two pieces are null.

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • Thats a nice explanation you got !!!

    There is also a thing to remember about the ISNULL behavior which is very useful while coding.

    Check below link:

    http://borngeek.in/understanding-isnulltransact-sql-behavior/

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply