Add a null value to a Varchar Value

Question

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..

Denis Valeev · Accepted Answer · 2010-09-22 12:29:11Z

6

Try this:

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

answered Sep 22, 2010 at 12:29

Denis Valeev

6,02537 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Marin Over a year ago

Great answer but it begs the question why in Sql null + string = null?

Denis Valeev Over a year ago

@Marin because NULL is something unknown; how much money will you have if you got unknown plus 3 bucks? Exactly unknown amount.

onedaywhen · Accepted Answer · 2010-09-22 13:02:12Z

3

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, ''), '')

answered Sep 22, 2010 at 13:02

onedaywhen

57.4k12 gold badges103 silver badges142 bronze badges

Comments

Peter Perháč · Accepted Answer · 2010-09-22 12:30:09Z

2

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

answered Sep 22, 2010 at 12:30

Peter Perháč

20.8k23 gold badges129 silver badges153 bronze badges

2 Comments

eumiro Over a year ago

If SURNAME_CONT is NULL, then you get NAME_CONT and a space. Do you want to have this trailing space there?

bAN Over a year ago

Not really important to have this Space ending the string, he is used to bind a dropdownlist.

Collectives™ on Stack Overflow

Add a null value to a Varchar Value

3 Answers 3

2 Comments

Comments

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

2 Comments

Related