Is there any efficient way of dealing null values during concat functionality of pyspark.sql version 2.3.4?

Question

As you can see in S.S if any attribute has a null value in a table then concatenated result become null but in SQL result is nonullcol + nullcol = nonullcol while in spark it is giving me null, suggest me any solution for this problem. Thanks in advance

Answer 1

When you concatenate any string with a NULL value, it will result in NULL. To avoid this, you can use the COALESCE function. 

spark.sql(SELECT COALESCE(Name, '') + ' '+ COALESCE(Column2, '') AS Result FROM table_test).show()

The COALESCE function returns the first non-Null value. So, when there is a value in the column that is not null, that will be concatenated. And if the value in the column is null, then an empty string will be concatenated.