Concatenate data from two fields ignoring NULL values in one of them?

Question

Dataset is following:

FirstName          LastName       city      street        housenum
john            silver            london   ukitgam         780/19
gret            garbo             berlin   akstrass         102
le              chen             berlin    oppenhaim        NULL
daniel          defo             rome      corso vinchi     25
maggi           forth           london     bolken str      NULL
voich           lutz            paris      pinchi platz     NULL
anna           poperplatz        milan     via domani       15/4

write following query:

SELECT Trim(a.FirstName) & ' ' & Trim(a.LastName) AS employee_name,
a.city, a.street + ' ' + a.housenum AS address
FROM Employees AS a

The result will be as this:

employee_name        city          address
john silver         london       ukitgam 780/19
gret garbo          berlin       akstrass 102
le chen             berlin       NULL
daniel defo         rome        corso vinchi 25
maggi forth         london       NULL
voich lutz          paris        NULL
anna poperplatz     milan      via domani 15/4

but I want this:

employee_name           city                  address
john silver            london               ukitgam 780/19
gret garbo             berlin               akstrass 102
le chen                berlin               oppenhaim
daniel defo            rome                corso vinchi 25
maggi forth            london              bolken str
voich lutz             paris               pinchi platz
anna poperplatz        milan             via domani 15/4

please help me.

MSeifert · Accepted Answer · 2018-02-26 09:31:12Z

5

Just use ISNULL() function for housenum column.

SELECT Trim(a.FirstName) & ' ' & Trim(a.LastName) AS employee_name,
a.city, a.street + ' ' + ISNULL(a.housenum,'') AS address
FROM Employees AS a

You are getting address as NULL when housenum column value is NULL because NULL concatenated with anything gives NULL as final result.

As has been posted in other answers , you can also use COALESCE() to handle NULL values.

COALESCE is in the SQL '92 standard and supported by more different databases. On other hand ISNULL() is provided in SQL Server only as so would not be much portable.

edited Feb 26, 2018 at 9:31

MSeifert

154k41 gold badges356 silver badges377 bronze badges

answered Jan 28, 2014 at 11:51

Mudassir Hasan

29k21 gold badges104 silver badges135 bronze badges

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

Comments

Mureinik · Accepted Answer · 2014-01-28 11:51:05Z

3

You can use COALESCE to handle the nulls:

SELECT Trim(a.FirstName) & ' ' & Trim(a.LastName) AS employee_name,
       Coa.city, a.street + Coalesce(' ' + a.housenum,'')  AS address
FROM Employees AS a

Note that I apply the coalesce to ' ' + a.housenum so in case housenum is null you won't get a trailing space.

answered Jan 28, 2014 at 11:51

Mureinik

316k54 gold badges400 silver badges405 bronze badges

Comments

vivek · Accepted Answer · 2014-01-28 12:04:56Z

2

try like this

  SELECT concat_ws(a.FirstName,a.LastName) AS employee_name,
 concat_ws(a.city, a.street,a.housenum) AS address
 FROM Employees AS a

as it ignores null . You will get the result you expected

answered Jan 28, 2014 at 12:04

vivek

3291 gold badge2 silver badges9 bronze badges

Comments

Suman Banerjee · Accepted Answer · 2014-01-28 11:54:46Z

1

SELECT Trim(a.FirstName) & ' ' & Trim(a.LastName) AS employee_name,
a.city, a.street & (' ' +a.housenum) AS address
FROM Employees AS a

answered Jan 28, 2014 at 11:54

Suman Banerjee

1,9614 gold badges24 silver badges40 bronze badges

Comments

cppcoder · Accepted Answer · 2014-01-28 12:13:53Z

1

You can use CONCAT(col1,col2) function instead of using + as follows:

SELECT Trim(a.FirstName) & ' ' & Trim(a.LastName) AS employee_name,
a.city, CONCAT(a.street,a.housenum) AS address
FROM Employees AS a

edited Jan 28, 2014 at 12:13

cppcoder

23.3k7 gold badges62 silver badges90 bronze badges

answered Jan 28, 2014 at 11:54

Sonia

111 bronze badge

1 Comment

Sonia Over a year ago

You can ask me here only if you have any more queries.

wegus · Accepted Answer · 2014-01-28 11:51:25Z

0

just replace

housenum

with

ISNULL ( housenum , '' )

The result would be the housenum or an empty string

answered Jan 28, 2014 at 11:51

wegus

2821 silver badge9 bronze badges

Collectives™ on Stack Overflow

Concatenate data from two fields ignoring NULL values in one of them?

6 Answers 6

Comments

Comments

Comments

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

Comments

Comments

Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related