CODEWITHSUNDEEP
mysqlsql
user1331534
user1331534

Reputation:

Mysql CONCAT() is not working properly

i have problem with CONCAT mysql function

Mysql Quesry is

SELECT CONCAT(a_firstname,' ',a_lastname) as a_employeename FROM employee.

This gives null if any of field is null

+--------------------------+
| a_firstname | a_lastname |
----------------------------
| bh          | a          |
| a           | NULL       |
+--------------------------+

Result 

+----------------+
| a_employeename |
------------------
| bh             |
| NULL           |
+----------------+

Upvotes: 4

Views: 13807

Answers (5)

Affan Pathan
Affan Pathan

Reputation: 308

MySQL treats NULL “no data”, So anything could be club with NULL become NULL. So to avoid this you must put IFNULL(field,result).

See following query:

SELECT CONCAT(IFNULL(a_firstname,''),' ',IFNULL(a_lastname,'')) as a_employeename FROM employee

Thanks.

Upvotes: 7

AKZap
AKZap

Reputation: 1211

just use IFNULL function

try this code

SELECT CONCAT(IFNULL(a_firstname,''),' ',IFNULL(a_lastname,'')) as a_employeename FROM employee.

Upvotes: 1

J A
J A

Reputation: 1766

If you want to concatenate both NULL and NOT NULL fields, CONCAT_WS() is the best solution. Example: CONCAT_WS(',', FIELD1,FIELD2,FIELD3) will return 'FIELD1Value,FIELD2Value,FIELD3Value'. If FIELD2Value is NULL, output will be 'FIELD1Value,FIELD3Value'.

Upvotes: 0

aF.
aF.

Reputation: 66697

When you make CONCAT and one of the fields is NULL, the result is always NULL.

Upvotes: 0

Jon
Jon

Reputation: 437376

Let's begin with a lesson in humility: CONCAT is working properly, but you are not using it right. Please don't jump to conclusions so eagerly.

The problem here is that you must use COALESCE when concatenating NULL values:

SELECT CONCAT(COALESCE(a_firstname, ''),' ', COALESCE(a_lastname, ''))

This is because any operation that involves NULL (such as concatenating NULL with a string) also results in NULL. See problems with NULL values.

Upvotes: 5

Related Questions