apache-spark-sqlazure-databricksdatabricks-sql

Replace empty strings with NULL in Azure Databricks SQL

I have a number of empty strings as a result of using array_join in the SQL.

However, there is no real need for me to differentiate between NULL values and empty strings.

Therefore, I would like to convert all empty strings in a column into NULL.

What is the best way to do this in SQL?

Does REPLACE work or is there a better way to do it ?

Solution

  • You can use NULLIF() function and replace the column having empty string with null value.

    select nullif(<column_name>,'') from <table-name>

    Nullif returns null if the 1st expression in it equals to the 2nd expression. Here, if column has empty string, then nullif returns null.