November 11, 2008 at 4:13 am
Reqno status remarks
1 Ok null
2 OK null
Select ReqNo, status + ' - ' + remarks
from table1
Result:
Reqno (no column name)
1 null
2 null
I tried to concatinate the two column but since the other column is null it is giving me null value. Even if remarks is null I want to see the status. Am I missing something? very basic but I am lost.
Regards
Dabsukol
November 11, 2008 at 4:25 am
You should use the ISNULL function to replace NULL values with an empty string.
Select status + ' - ' ISNULL(remarks,'')
[font="Verdana"]Markus Bohse[/font]
November 11, 2008 at 4:26 am
SqlUser (11/11/2008)
Reqno status remarks1 Ok null
2 OK null
Select ReqNo, status + ' - ' + remarks
from table1
Result:
Reqno (no column name)
1 null
2 null
I tried to concatinate the two column but since the other column is null it is giving me null value. Even if remarks is null I want to see the status. Am I missing something? very basic but I am lost.
Regards
Dabsukol
Concatenating null to any value will produce null as results. You can change it by modifying the set options for your session (for example you can SET CONCAT_NULL_YIELDS_NULL to off), but the better way is to use the isnull function in your query:
Select ReqNo, status, + isnull(' - ' + remarks, '')
from table1
Notice that I included the concatenating of the - sign, so if remarks is null, you won't see it in the results. If you do want to see it, you should include only the column remark in the isnull function.
You can find more details about the isnull function in Books On Line.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
November 11, 2008 at 4:28 am
SqlUser (11/11/2008)
Reqno status remarks1 Ok null
2 OK null
Select ReqNo, status + ' - ' + remarks
from table1
Result:
Reqno (no column name)
1 null
2 null
I tried to concatinate the two column but since the other column is null it is giving me null value. Even if remarks is null I want to see the status. Am I missing something? very basic but I am lost.
Regards
Dabsukol
try like this:
select ReqNo, isnull (Status, '') + ' - ' + isnull (Remarks, '') from table1;
🙂
November 11, 2008 at 11:13 pm
Thanks.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply