Post reply

Select statement is giving null value

  • November 11, 2008 at 4:13 am

    #201090

    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

    #896851

    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

    #896852

    SqlUser (11/11/2008)

    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

    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

    #896853

    SqlUser (11/11/2008)

    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

    try like this:

    select ReqNo, isnull (Status, '') + ' - ' + isnull (Remarks, '') from table1;

    🙂

  • November 11, 2008 at 11:13 pm

    #897344

    Thanks.

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply