I use + to concatenate several columns's value. But + doesnt work if one of that columns has null value. For example
Select null+ 'Test'
query returns null instead of 'Test'.
What are your advices to solve that problem?
Add a comment
|
2 Answers
On versions prior to SQL Server 2012 you should use
Select ISNULL(YourColumn,'') + 'Test' /*Or COALESCE(YourColumn,'')*/
to avoid this issue.
There is a connection option SET CONCAT_NULL_YIELDS_NULL OFF but that is deprecated.
SQL Server 2012 introduces the CONCAT function that treats NULL as an empty string when concatenating.
SELECT CONCAT(null,'Test')
4 Comments
jhash
is there another way to solve it?I am migrating my project from oracle to mssql,i dont wanna add isnull control to every control
Martin Smith
@jhash - The only 3 ways I am aware of are in my answer already.
ISNULL,COALESCE, or CONCAT_NULL_YIELDS_NULL. I'd just add COALESCE to all the relevant places as that is standard and will work in both Oracle and SQL Server. Up to you if you want to use deprecated features but they will cease to work in future versions and this connection option is not compatible with some SQL Server functionality. I wouldn't use it myself.Martin Smith
@jhash - Although actually I am doubting my assertion that it will work on Oracle now.
COALESCE definitely will but AFAIK Oracle treats the empty string as NULL so I have no idea what the net result will be!Martin Smith
Not that it probably matters as Oracle doesn't use
+ for string concatenation anyway.Use IsNull :
SELECT IsNull(MyColumn, '') + 'Test' ...
