Spark (scala) dataframes - Check whether strings in column exist in a column of another dataframe

I have a spark dataframe, and I wish to check whether each string in a particular column contains any number of words from a pre-defined a column of another dataframe.

Here is the way. using = or like

package examples

import org.apache.log4j.Level
import org.apache.spark.sql.SparkSession
import org.apache.spark.sql.functions.{col, _}

object CompareColumns extends App {
  val logger = org.apache.log4j.Logger.getLogger("org")
  logger.setLevel(Level.WARN)
  val spark = SparkSession.builder()
    .appName(this.getClass.getName)
    .config("spark.master", "local").getOrCreate()

  import spark.implicits._

  val df1 = Seq(

    (1, "google.com"),
    (2, "facebook.com"),
    (3, "github.com"),
    (4, "stackoverflow.com")).toDF("id", "url").as("first")
  df1.show
  val df2 = Seq(
    (11, "google.com"),
    (12, "yahoo.com"),
    (13, "facebook.com"),
    (14, "twitter.com")).toDF("id", "url").as("second")
  df2.show
  val df3 = df2.join(df1, expr("first.url  like  second.url"), "full_outer").select(
    col("first.url")
    , col("first.url").contains(col("second.url")).as("check")).filter("url is not null")
  df3.na.fill(Map("check" -> false))
    .show


}

Result :

+---+-----------------+
| id|              url|
+---+-----------------+
|  1|       google.com|
|  2|     facebook.com|
|  3|       github.com|
|  4|stackoverflow.com|
+---+-----------------+

+---+------------+
| id|         url|
+---+------------+
| 11|  google.com|
| 12|   yahoo.com|
| 13|facebook.com|
| 14| twitter.com|
+---+------------+

+-----------------+-----+
|              url|check|
+-----------------+-----+
|       google.com| true|
|     facebook.com| true|
|       github.com|false|
|stackoverflow.com|false|
+-----------------+-----+

with full outer join we can achive this... For more details see my article with all joins here in my linked in post

Note : Instead of 0 for false 1 for true i have used boolean conditions here.. you can translate them in to what ever you wanted...

UPDATE : If rows are increasing in second dataframe you can use this, it wont miss any rows from second

val df3 = df2.join(df1, expr("first.url  like  second.url"), "full").select(
    col("second.*")
    , col("first.url").contains(col("second.url")).as("check"))
    .filter("url is not null")
  df3.na.fill(Map("check" -> false))
    .show

Also, one more thing is you can try regexp_extract as shown in below post

https://stackoverflow.com/a/53880542/647053