Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Isnull behavior Expand / Collapse
Author
Message
Posted Wednesday, July 29, 2009 11:02 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: 2 days ago @ 6:12 AM
Points: 65, Visits: 176
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

Post #761808
Posted Wednesday, July 29, 2009 11:19 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Monday, December 8, 2014 1:04 PM
Points: 3,840, Visits: 3,854
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 - by Jeff Moden
Post #761822
Posted Wednesday, July 29, 2009 11:23 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: 2 days ago @ 6:12 AM
Points: 65, Visits: 176
That is what i wanted to hear! Thanks!
Post #761824
Posted Wednesday, July 29, 2009 11:25 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:28 AM
Points: 7,179, Visits: 15,775
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?
Post #761826
Posted Monday, October 14, 2013 6:23 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, July 2, 2014 8:22 AM
Points: 148, Visits: 185
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/

Post #1504420
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse