cancel
Showing results for 
Search instead for 
Did you mean: 
Get Started Discussions
Start your journey with Databricks by joining discussions on getting started guides, tutorials, and introductory topics. Connect with beginners and experts alike to kickstart your Databricks experience.
cancel
Showing results for 
Search instead for 
Did you mean: 

exclude (not like) filter using pyspark

abueno
New Contributor III

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)

1 ACCEPTED SOLUTION

Accepted Solutions

brockb
Databricks Employee
Databricks Employee

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.

View solution in original post

2 REPLIES 2

brockb
Databricks Employee
Databricks Employee

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
New Contributor III

Worked perfectly Thank you. 

Join Us as a Local Community Builder!

Passionate about hosting events and connecting people? Help us grow a vibrant local community—sign up today to get started!

Sign Up Now