Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Hommer
Aged Yak Warrior
808 Posts |
 Posted - 2006-10-06 : 16:54:29
|
| Hi, all,I run into a problem when I tried to concat 3 strings into three line of texts and if any of the 3 did not return any thing, the whole string is nothing. Here is the example.declare @text as varchar(8000)declare @text1 as varchar(100)declare @text2 as varchar(100)declare @text3 as varchar(100)select @text1 ='my first text string 'select @text2 = ' 'my second text string 'select @text3 = ' 'my third text string 'select @text = @text1 +char(10) + char(13)+ @text2 +char(10) + char(13) + @text3 print @text The above got the right outcome, however, it requiresall 3 of the select (@tex1, @text2, and @text3) to return something, even ' ' is fine. If one of them is commented out, then the @text shows nothing. What is the right way to make @text return regardless one or two of the local vars did not get populated?Thanks! |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-10-06 : 17:07:23
|
| You can use COALESCE on each string to force '' when it's NULL, or you can use SET CONCAT_NULL_YIELDS_NULL OFF.Tara Kizer |
 |
|
|
Hommer
Aged Yak Warrior
808 Posts |
Posted - 2006-10-06 : 17:17:30
|
| Aha, Thank you! |
|
|
|
|
|
|
String concat help