News, links, downloads, tips and tricks on Microsoft Access and related

About Me Search
Name:Alex Dybenko

Location:Moscow, Russia

Tuesday, March 27, 2007

Be careful using ISNULL()

Recently I found quite unpredictable (for me) behavior of SQL Server ISNULL() function. If you use it in Select, both arguments are nvarchar and length of first is less then second – then value of second field will be truncated to the length of first. So, if you run this SQL:

SELECT Address,
ISNULL(Region, Address)
FROM Suppliers

on sample Northwind database – result will be:

9-8 Sekimai Musashino-shi|9-8 Sekimai Mus
92 Setsuko Chuo-ku 92|Setsuko Chuo
Lyngbysild Fiskebakken 10|Lyngbysild Fisk

Any idea why this happens? Fortunately – COALESCE() works correct in this case, so I would recommend using it instead of ISNULL().


Technorati tags: ,


Anonymous Anonymous said...

thanks for the plug Alex. Garry

4:03 PM  

Post a Comment

<< Home