is there a way to concatenate strings in mysql where any of the strings may be null?-CodePudding

I need to concatenate a couple of dozen strings into one string and the concat function returns a null if any of the strings are null. is there a function that ignores nulls in mysql?

CodePudding user response：

You can use the COALESCE operator for this as COALESCE(yourString, ''), this will return your string if not null or an empty string it it's null.

From the docs: Returns the first non-NULL value in the list, or NULL if there are no non-NULL values.

https://dev.mysql.com/doc/refman/8.0/en/comparison-operators.html#function_coalesce

CodePudding user response：

You can use CONCAT_WS which skips nulls:

SELECT CONCAT_WS(' ', 'string 1', NULL, 'string 2')
-- string 1 string 2

You can use '' as the separator, too.