Search code examples
sqlsql-serverbackspace

is there any `backspace` character in mssql for string?

Below is the query to form a string containing company details by concatenating Name, address1 and address2. Here I am using the ISNULL() function to concatenate an empty string ('') if the column is null. 

select Name+' ,'+isnull(Address1,'')+' ,'+isnull(Address2,'') as compDeatils 
from tableCompany where ID = 4

This query's issue is in the case where Address1 or Address2 is null, it will concatenate a comma before the empty space which I don't want.

For example, if Address1 and Address2 are NULL then the result will be name,,.

How i can overcome this?

Is there a backspace character in mssql?

Solution

  • i got the answer it is simple logic.. add commas within the ISNULL() function

    select Name+isnull(','+Address1,'')+isnull(','+Address2,'') as compDeatils 
from tableCompany where ID = 4

    Note: Name will not give NULL in my case. in case name also null this answer is not applicable.