0

Not able to convert the below T-SQL Query part ISNULL(NAME,'N/A') to Spark-SQL Equivalent

SELECT ID, ISNULL(NAME,'N/A') AS NAME, COMPANY FROM TEST to

convert the below T-SQL Query part ISNULL(NAME,'N/A') to Spark-SQL Equivalent

SELECT ID, ISNULL(NAME,'N/A') AS NAME, COMPANY FROM TEST

Nagesh Rudraiah
  • 5
  • 3

2 Answers2

1

You can do it in two ways, like so:

df = spark.createDataFrame([(1, None), (2, None)], "id: int, value: string")
df.show()

+---+-----+
| id|value|
+---+-----+
|  1| null|
|  2| null|
+---+-----+

df.na.fill("N/A", subset=["value"]).show()

+---+-----+
| id|value|
+---+-----+
|  1|  N/A|
|  2|  N/A|
+---+-----+

from pyspark.sql.functions import col, when

df.withColumn("value", when(col("value").isNull(), "N/A")).show()

+---+-----+
| id|value|
+---+-----+
|  1|  N/A|
|  2|  N/A|
+---+-----+

Either option gives you the same result.

Bartosz Gajda
  • 984
  • 6
  • 14
0

The function isnull() merely returns a boolean stating if the input was null or null. Alternatively try using an expression within a case statement or (coalesce)[https://docs.databricks.com/sql/language-manual/functions/coalesce.html]

CASE WHEN NAME IS NULL THEN 'N/A' ELSE NAME END AS NAME

OR

SELECT COALESCE(NAME,'N/A') AS NAME

Hope it helps...

rainingdistros
  • 450
  • 3
  • 11