Myujth

I have a dataframe with column having values like "COR//xxxxxx-xx-xxxx" or "xxxxxx-xx-xxxx"

I need to compare this column with another column in a different dataframe based on the column value.

1. If column value have "COR//xxxxx-xx-xxxx", I need to use substring("column", 4, length($"column")
   


2. If the column value have "xxxxx-xx-xxxx", I can compare directly without using substring.

For example:

val DF1 = DF2.join(DF3, upper(trim($"column1".substr(4, length($"column1")))) === upper(trim(DF3("column1"))))

I am not sure how to add the condition while joining. Could anyone please let me know how can we achieve this in Spark dataframe?

You can try adding a new column based on the conditions and join on the new column. Something like this.

val data = List("COR//xxxxx-xx-xxxx", "xxxxx-xx-xxxx")
val DF2 = ps.sparkSession.sparkContext.parallelize(data).toDF("column1")
val DF4 = DF2.withColumn("joinCol", when(col("column1").like("%COR%"),
 expr("substring(column1, 6, length(column1)-1)")).otherwise(col("column1")) )

DF4.show(false)

The new column will have values like this.

+------------------+-------------+
|column1 |joinCol |
+------------------+-------------+
|COR//xxxxx-xx-xxxx|xxxxx-xx-xxxx|
|xxxxx-xx-xxxx |xxxxx-xx-xxxx|
+------------------+-------------+

You can now join based on the new column added.

val DF1 = DF4.join(DF3, upper(trim(DF4("joinCol"))) === upper(trim(DF3("column1"))))

Hope this helps.

Simply create a new column to use in the join:

DF2.withColumn("column2", 
 when($"column1" rlike "COR//.*", 
 $"column1".substr(lit(4), length($"column1")).
 otherwise($"column1"))

Then use column2 in the join. It is also possible to add the whole when clause directly in the join but it would look very messy.

Note that to use a constant value in substr you need to use lit. And if you want to remove the whole "COR//" part, use 6 instead of 4.