Menu
Using the + Operator It simply checks for the null reference of the input String object. If the input object is null, it returns an empty (“”) String, otherwise, it returns the same String: return value == null ?
Concatenating Data When There Are NULL ValuesTo resolve the NULL values in string concatenation, we can use the ISNULL() function. In the below query, the ISNULL() function checks an individual column and if it is NULL, it replaces it with a space.
Checks if the value is null, empty, or contains only whitespace characters. Returns true if the string is null, empty, or only whitespace.
convert the NULL values with empty string by wrapping it in COALESCE
SELECT CONCAT(COALESCE(`affiliate_name`,''),'-',COALESCE(`model`,''),'-',COALESCE(`ip`,''),'-',COALESCE(`os_type`,''),'-',COALESCE(`os_version`,'')) AS device_name
FROM devices
Use CONCAT_WS instead:
CONCAT_WS() does not skip empty strings. However, it does skip any NULL values after the separator argument.
SELECT CONCAT_WS('-',`affiliate_name`,`model`,`ip`,`os_type`,`os_version`) AS device_name FROM devices
CONCAT_WS still produces null for me if the first field is Null. I solved this by adding a zero length string at the beginning as in
CONCAT_WS("",`affiliate_name`,'-',`model`,'-',`ip`,'-',`os_type`,'-',`os_version`)
however
CONCAT("",`affiliate_name`,'-',`model`,'-',`ip`,'-',`os_type`,'-',`os_version`)
produces Null when the first field is Null.
To have the same flexibility in CONCAT_WS as in CONCAT (if you don't want the same separator between every member for instance) use the following:
SELECT CONCAT_WS("",affiliate_name,':',model,'-',ip,... etc)
SELECT CONCAT(isnull(`affiliate_name`,''),'-',isnull(`model`,''),'-',isnull(`ip`,''),'-',isnull(`os_type`,''),'-',isnull(`os_version`,'')) AS device_name
FROM devices
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With