CODEWITHSUNDEEP
sqlsql-server
bAN
bAN

Reputation: 13825

Add a null value to a Varchar Value

I have 2 columns with "name" and "surname" I want to return a result with the two concatenated.

but I have a problem, the surname column accept null values and when it's the case the concatenation is null.. I would like in this case just to have the NAME

here is 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's works when Surname is blank or fulled..

Tx a lot..

Upvotes: 4

Views: 3633

Answers (3)

onedaywhen
onedaywhen

Reputation: 57023

Consider omitting the space separating character when SURNAME_CONT is the NULL value. Also consider handling when SURNAME_CONT is the empty string. COALESCE is Standard SQL e.g. 

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

Upvotes: 3

Peter Perháč
Peter Perháč

Reputation: 20782

there's a function ISNULL(expression, replacement_value)

refer to msdn manual

so you could do:

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

Upvotes: 2

Denis Valeev
Denis Valeev

Reputation: 6015

Try this: 

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

Upvotes: 6

Related Questions