exclude (not like) filter using pyspark

[abueno]

I am trying to exclude rows with a specific variable when querying using pyspark but the filter is not working.  Similar to the "Not like" function in SQL.  e.g. not like '%var4%'.  The part of the code that is not working is: (col('col4').rlike('var4') == False)
Code:

%python
from pyspark.sql.functions import col

fc_run = spark.table("tbl1")

flowchart_run = fc_run.select(
'col1',
'col2',
'col3',
'col4',

).filter(
  (col('col1') == 'var1') &
(col('col2').rlike('var2')) &
(col('col3').rlike('var3')) &
(col('col4').rlike('var4') == False)

)

display(flowchart_run)

[brockb · Accepted solution]

Hi @abueno ,

To replicate a SQL `not like '%var4%'` clause in the Dataframe API, you could use `rlike` with negation using `~` such as:

df.filter(~col('col4').rlike('var4')).display()

Here's a basic reproducible example:

df = (spark.range(10).withColumn("col4", f.lit("var3"))).union(
      spark.range(10).withColumn("col4", f.lit("var4")))

df.filter(~col('col4').rlike('var4')).groupBy('col4').count().display()
col4	count
var3	10

Hope this helps.

[abueno]

Worked perfectly Thank you.