CONCAT return null

Question

I am trying to get three columns as a string but for some reason I only get NULL.

this is the code:

SELECT CONCAT(name, ' ', city, ' ', country) FROM places

the table places contains the three columns name, city and country and it is not empty, however, it can contain NULL in some of the columns.

The result:

NULL
NULL
NULL,
etc

I tried the answer from this post MySQL CONCAT returns NULL if any field contain NULL and it doesn't work for me.

and what is in result when `SELECT name, city, country, CONCAT(name, ' ', city, ' ', country) FROM places` ? — Alex, May 16 '19 at 17:59
It shows all the columns and the column with the concant still all null — Abdel Hidalgo, May 16 '19 at 18:01
I disagree with the duplicated question, the answer in this post is not working for me — Abdel Hidalgo, May 16 '19 at 18:06

Arulkumar · Accepted Answer · 2019-05-16T18:10:42.833

3

Can you try with CONCAT_WS()

SELECT CONCAT_WS(' ', name, city, country) FROM places

edited May 16 '19 at 18:10

answered May 16 '19 at 18:02

Arulkumar

12,966
14
47
68

1

Didn't know about this. Good answer! – Joe Phillips May 16 '19 at 18:03
this is the response – Abdel Hidalgo May 16 '19 at 18:04
It's not just MySQL 8.0. This function has existed in MySQL for years. If only people would read the documentation... For example, here's the link to the MySQL 5.5 doc on this function: https://dev.mysql.com/doc/refman/5.5/en/string-functions.html#function_concat-ws – Bill Karwin May 16 '19 at 18:08
It is definitively the first time I see a function like this one – Abdel Hidalgo May 16 '19 at 18:11
@BillKarwin Thanks for pointing out, I have rolledback my edit – Arulkumar May 16 '19 at 18:11

score 1 · Answer 2 · answered May 16 '19 at 17:57

1

You probably want to use something like COALESCE(name, '') to convert a null string to an empty string. Since null means unknown, mysql will not assume it means empty string. Unknown concatenated with anything is still unknown. You have to tell it explicitly to treat it as empty string and that is what the coalesce is for.

answered May 16 '19 at 17:57

Joe Phillips

49,743
32
103
159

it shows a weird result, with only some partial columns, not all the info that it should show. – Abdel Hidalgo May 16 '19 at 18:02

CONCAT return null

2 Answers2