March 7, 2002 at 8:11 am
Hello, I am new to SQL and to this site so thank you in advance for any support offered. I'm in the process of upsizing an access db to sql and I created a view to use with the access front end but I'm experiencing problems when concatenating fields. I'm concatenating fname, lname and mi but when mi is null, the entire field result is null. I'm attempting to change the default using sp_dboption. This is what I did...
USE master
EXEC sp_dboption 'model', 'concat null yields null', 'FALSE'
GO
I've also tried using the set statement for this setting without success.
Can somebody put me on the right path (syntax)?
Thanks!!
March 7, 2002 at 8:24 am
Try ISNULL(mi,'') instead of just mi. This checks for NULL as the value and replaces with '' string.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
March 7, 2002 at 8:57 am
That did it! Thanks so much, I really appreciate the help!
March 7, 2002 at 9:02 am
Sorry for the duplicate post!
March 7, 2002 at 8:16 pm
You can override the behavior by doing this:
Syntax
SET CONCAT_NULL_YIELDS_NULL { ON | OFF }
This gives you a much more granular way of applying the setting - I will often turn it off before a series of statements then toggle it back on for the rest. Nulls propagating is new in SQL7, in SQL 6.5 it would return a string if either parameter was a string. Something to watch for when doing the 6.5 to 7/2000 upgrade, you can always temporarily fix by putting the compatability mode back to 6.5
Nothing wrong with IsNull as a solution. Use whichever seems easiest. I have not timed to see if there is any difference in execution time, I would expect it to be minimal if any.
Andy
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply