Add null to Varchar

Question

I have 2 columns with "first name" and "last name". I want to return the result with two concatenated ones.

but I have a problem, the last name column is NULL, and when this is the case, the join is null .. I would like to just have NAME in this case

here is the code:

SELECT c.ID_CONT, c.ID_TYPE_CONTACT, c.ID_PARAM_CENTRE, c.FONCTION_CONT, c.MEMO_CONT, c.VISIBLE_CONT, c.NAME_CONT +' '+c.SURNAME_CONT as NAMESURNAME FROM dbo.CONTACT c 

It works when the last name is empty or filled out.

Tx a lot ..

Answer 1

Try the following:

 isnull(c.NAME_CONT +' ', '')+isnull(c.SURNAME_CONT,'') 

Answer 2

Consider the omission of the separator when SURNAME_CONT is NULL. Also consider handling when SURNAME_CONT is an empty string. COALESCE - standard SQL, for example.

 c.NAME_CONT + COALESCE(' ' + NULLIF(c.SURNAME_CONT, ''), '') 

Answer 3

there is an ISNULL function (expression, replacement_value)

refer to msdn manual

so you can:

 SELECT c.ID_CONT, c.ID_TYPE_CONTACT, c.ID_PARAM_CENTRE, c.FONCTION_CONT, c.MEMO_CONT, c.VISIBLE_CONT, c.NAME_CONT +' '+ISNULL(c.SURNAME_CONT, '') as NAMESURNAME FROM dbo.CONTACT c