July 29, 2009 at 11:02 am
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
July 29, 2009 at 11:19 am
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.
July 29, 2009 at 11:23 am
That is what i wanted to hear! Thanks!
July 29, 2009 at 11:25 am
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?
October 14, 2013 at 6:23 am
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