Apply multiple LIKE filters for a Dataframe

user_3792360 0
Viewed 31

Is there a better implementation in Spark SQL by regexp_like for the following

SELECT col1,
       col2
FROM fact_table
WHERE UPPER((TRIM(NAME))) LIKE 'VAL1 %'
  OR UPPER((TRIM(NAME))) LIKE '% VAL1 %'
  OR UPPER((TRIM(NAME))) ='VAL1'
  OR UPPER((TRIM(NAME))) LIKE 'VAL1-%'
  OR UPPER((TRIM(NAME))) LIKE 'VAL2 %'
  OR UPPER((TRIM(NAME))) LIKE '% VAL2 %'
  OR UPPER((TRIM(NAME)))='VAL2'
  OR UPPER((TRIM(NAME))) LIKE 'VAL2-%'
apache-spark sql
1 Answers

In raw SQL no. With Dataframe API you can use higher-level functions to combine the filter expressions:

val filterExpr = Seq("%VAL1 %", "% VAL1%", "VAL1" /* ... */)
                   .map(x => upper(trim($"name")).like(x))
                   .reduce(_ || _)
val df = Seq("MYVAL1 X", "FOO", "val1", "AVAL1").toDF("name").filter(filterExpr).show

+--------+
|    name|
+--------+
|MYVAL1 X|
|    val1|
+--------+

Alternatively you could also write a UDF.

user_28900930
Related