Apache Spark startsWith in SQL expression

Question

In Apache Spark API I can use startsWith function in order to test the value of the column:

myDataFrame.filter(col("columnName").startsWith("PREFIX"))

Is it possible to do the same in Spark SQL expression and if so, could you please show an example?.

Answer 1

With regexp_replace, you can get the same results without UDFs. Check this out

scala> val df=Seq(("bPREFIX",1),("PREFIXb",2)).toDF("a","b")
df: org.apache.spark.sql.DataFrame = [a: string, b: int]

scala> df.show
+-------+---+
|      a|  b|
+-------+---+
|bPREFIX|  1|
|PREFIXb|  2|
+-------+---+


scala> df.filter(regexp_replace('a,"""^PREFIX.*""","")==="").show
+-------+---+
|      a|  b|
+-------+---+
|PREFIXb|  2|
+-------+---+


scala>

or using regexp_extract()

scala> df.filter(regexp_extract('a,"""(^PREFIX)""",1)==="PREFIX").show
+-------+---+
|      a|  b|
+-------+---+
|PREFIXb|  2|
+-------+---+


scala>

Using instr() function

scala> df.filter("instr(a,'PREFIX')=1").show
+-------+---+
|      a|  b|
+-------+---+
|PREFIXb|  2|
+-------+---+


scala>

Answer 2

I found the following solution that works with Spark SQL query without custom UDP and out of the box, for example:

CASE WHEN instr(PRICE_SOURCE_INDICATOR,'MAC') = 1

Answer 3

You can create an UDF to do that

import org.apache.spark.sql.functions
val startsWith = udf((columnValue: String) => columnValue.startsWith("PREFIX"))

The UDF will receive the column and check it against the PREFIX, then you can use it as follows:

myDataFrame.filter(startsWith($"columnName"))

If you want a parameter as prefix you can with lit

val startsWith = udf((columnValue: String, prefix:String) => columnValue.startsWith(prefix))
myDataFrame.filter(startsWith($"columnName", lit("PREFIX")))

INPUT EXAMPLE

+-----------+------+
|letter     |number|
+-----------+------+
|    PREFIXb|     1|
|    bPREFIX|     2|
+-----------+------+

OUTPUT EXAMPLE

myDataFrame.filter(startsWith($"letter")).show
+-----------+------+
|letter     |number|
+-----------+------+
|    PREFIXb|     1|
+-----------+------+