concatenate with null values in mysql

Question

concatenate with null values in mysql

Navigation

#1 by (3 votes)
#2 by (1 votes)

2

I have the following:

|ap_pat|ap_mat|pri_nombre|seg_nom|

|Jara  |Rios  |Luis      |ivan   |

|lopez |NULL  |Jorge     |alf    |

Concatenating with CONCAT or CONCAT_WS gives me a space of more between each data if this NULL shows it to me like this:

Jara Rios Luis Ivan

Lopez |*| Jorge Alf

You see that the second value shows a space of plus |*| between ap_pat and nombre as I can do so that you omit that space

mysql php

asked by Francisco 02.03.2017 в 19:21

source

2 answers

How to pass components as parameters in react? Problem with Http post Angularjs2

score 3 · Answer 1

There is something that you have not explained well, because the use of CONCAT_WS with a separator ' ' should work perfectly. For example, if we make the following query:

select concat_ws(' ', 'Lopez', null, 'Jorge', 'alf');

The result is ( Demo ):

Lopez Jorge alf

... without any additional space.

More complete demo

My suspicion is that where you think you have a NULL you actually have an empty string. In that case, it would add a space of more.

For example, if we modify the query to:

select concat_ws(' ', 'Lopez', '', 'Jorge', 'alf');

Then the result is ( Demo ):

Lopez  Jorge alf

... now with an additional space.

score 1 · Answer 2

You can use the function IFNULL of MySQL. With this function you can return an alternative value if the data is NULL :

SELECT
    CONCAT( IFNULL(CONCAT(ap_pat, ' '), ''),
        IFNULL(CONCAT(ap_mat, ' '), ''),
        IFNULL(CONCAT(pri_nombre, ' '), ''),
        IFNULL(seg_nom, '') )

FROM tabla;

In this example, if any of the surnames or names are NULL , it will show an empty string for that data.