In PySpark, understanding the concept of like() vs rlike() vs ilike() is essential, especially when working with text data. You can use these functions to filter rows based on specific patterns, such as checking if a name contains both uppercase and lowercase letters or ends with a certain keyword. PySpark makes it easy to handle such cases with its powerful set of string functions.
In this article, I will explain the like(), rlike(), and ilike() functions in PySpark, how they are used for processing text data, and the key differences between them.
like()– Performs SQL-style pattern matchingrlike()– Uses regular expressions for pattern matchingilike()– Performs case-insensitive SQL-style pattern matching
By the end of this article, you will understand:
- How each function works
- The main differences between them
- Practical examples for real-world use cases
- Best practices for effective usage
- Answers to frequently asked questions to clear up common doubts
Key points-
- Use
like()for SQL-style wildcard matching with%and_. like()is case-sensitive, mind the letter casing.- Use
rlike()for powerful regex-based pattern matching. rlike()supports expressions like(?i)to ignore case.- Use
ilike()for case-insensitive SQL-style matching (Spark 3.0+). ilike()simplifies matching without regex when case doesn’t matter.- Use
filter()orwhere()with these functions to apply conditions. - Cast numeric columns to strings before pattern matching.
rlike()is best for complex or conditional text patterns.- Always choose the function that best fits your case sensitivity and pattern complexity.
<a id="h-like-sql-style-pattern-matching"></a>
like() – SQL-style Pattern Matching
The like() function is used to perform SQL-style pattern matching in PySpark. It checks if a string column matches a specified pattern using wildcard characters.
<a id="h-syntax"></a>
Syntax
Following is the syntax of the SQL like().
# Syntax of the like() function
col("column_name").like("pattern")
<a id="h-parameters"></a>
Parameters
pattern (str): SQL-style string pattern using %(any number of characters) and _ (single character)wildcards.
<a id="h-return-value"></a>
Return Value
It returns a column of boolean values. Each value is True if the corresponding row's value matches based on the pattern, otherwise False.
<a id="h-usage-of-like"></a>
Usage of like()
You can use the like() function in PySpark to filter rows in a DataFrame by applying SQL-style pattern matching to specific columns. Let’s create a DataFrame and apply the like() function to the target column (where you want to match string patterns using SQL expressions) to retrieve the filtered rows.
# Use like() function to filter rows of DataFrame
# Create SparkSession
from pyspark.sql import SparkSession
spark = SparkSession.builder.appName('AIStudyLabs.com').getOrCreate()
# Create data frame
data = [(1,"James Smith"), (2,"Michael Rose"),
(3,"Robert Williams"), (4,"Rames Rose"),(5,"Rames rose")]
df = spark.createDataFrame(data=data,schema=["id","name"])
df.show()
# Filter where name contains Rose string
# Import col from sql functions in pyspark
from pyspark.sql.functions import col
df.filter(col("name").like("%Rose%")).show()
Yields below the output.
From the above code, the function filters the DataFrame rows based on a case-sensitive match.
<a id="h-rlike-regex-matching"></a>
rlike() – Regex Matching
In PySpark, the rlike() function is used to apply regular expressions to string columns for advanced pattern matching. It accepts a regex string as a parameter and returns a Boolean column indicating whether each row matches the expression. By default, rlike() is case-sensitive. To make it case-insensitive, you must modify the regex pattern (e.g., using (?i) inline flag). Compared to like(), rlike() offers greater power and flexibility.
<a id="h-rlike-syntax"></a>
rlike() Syntax
Following is a syntax of rlike() function.
# Syntax of the rike() function
col("column_name").rlike("regex_pattern")
<a id="h-parameters-0"></a>
Parameters
regex_pattern (str): A valid regular expression pattern.
<a id="h-return-value-0"></a>
Return Value
Returns a boolean column where each element in the Column is matched by an extended regex expression.
<a id="h-usage"></a>
Usage
Let’s see an example of using rlike() to evaluate a regular expression. In the below examples, I use rlike() function to filter the PySpark DataFrame rows by matching patterns with regular expressions, including case-insensitive matching and filtering rows based on specific column values.
# Match names ending with 'rose', case-insensitive
from pyspark.sql.functions import col
# Filter rows by cheking value contains in anohter column by ignoring case
df.filter(col("name").rlike("(?i)rose")).show()
Yields below the output.
# Output:
+---+------------+
| id| name|
+---+------------+
| 2|Michael Rose|
| 4| Rames Rose|
| 5| Rames rose|
+---+------------+
If you don't use a case-insensitive flag, rlike() matches patterns in a case-sensitive manner by default.
# Match names ending with 'rose', case-sensitive
df.filter(col("name").rlike("rose")).show()
Yields below the output.
# Output:
+---+----------+
| id| name|
+---+----------+
| 5|Rames rose|
+---+----------+
<a id="h-ilike-case-insensitive-sql-style-pattern-matching"></a>
ilike()-Case-insensitive SQL-style Pattern Matching
The ilike() function is similar to like() but performs case-insensitive matching. This is useful when you want simple wildcard matching without worrying about letter casing.
<a id="h-ilike-syntax"></a>
ilike() Syntax
Following is the syntax of ilike().
# Syntax of the ilike() function
col("column_name").ilike("pattern")
<a id="h-parameters-1"></a>
Parameters
pattern (str): SQL-style string pattern using % and _matched case-insensitively.
<a id="h-return-value-1"></a>
Return Value
Returns a column of boolean values.
<a id="h-usage-of-ilike-function"></a>
Usage of ilike() Function
Let’s apply the ilike() function on PySpark DataFrame columns to perform case-insensitive SQL-style pattern matching. This function filters rows based on case-insensitivestring patterns using SQL expressions.
# Case-insensitive match using SQL-style wildcard
from pyspark.sql.functions import col
df.filter(col("name").ilike("%rose%"))
Yields below the output.
# Output:
+---+------------+
| id| name|
+---+------------+
| 2|Michael Rose|
| 4| Rames Rose|
| 5| Rames rose|
+---+------------+
<a id="h-differences-between-pyspark-like-rlike-and-like"></a>
Differences Between PySpark like(), rlike(), and like()
The table below highlights the key differences among these functions to help you understand when to use each one effectively.
<a id="h-best-practices"></a>
Best Practices
- Always cast numeric columns to string before using these functions
- Use
like()for simple wildcard matching when case matters. - Use
ilike()for case-insensitive SQL-style matching (Spark 3.0+). - Use
rlike()for advanced or flexible pattern matching with regex.
Example:
You can convert the numeric column to a string before filtering rows using pattern-matching functions such as like(), rlike(), or ilike().
# Filter where id ends with 5
from pyspark.sql.functions import col
df.filter(col("id").cast("string").like("%5")).show()
Yields below the output.
# Output:
+---+----------+
| id| name|
+---+----------+
| 5|Rames rose|
+---+----------+
<a id="h-frequently-asked-questions-of-pyspark-like-vs-rlike-vs-ilike"></a>
Frequently Asked Questions of PySpark like() vs rlike() vs ilike()
like() uses SQL-style pattern matching with % and _.
rlike() uses regular expressions, which are more powerful and flexible.
Yes, like() is case-sensitive. For example, %Rose% will not match rose.
Use either:
ilike() for SQL-style patterns (Spark 3.0+), or
rlike("(?i)pattern") for regex-based matching.
ilike() is the case-insensitive version of like(), introduced in Spark 3.0+. It uses SQL-style wildcards.
% → matches any sequence of characters
_ → matches a single character
like() and ilike() only support SQL wildcards. Use rlike() for regex.
Use: col("name").rlike("(?i)rose$")
This matches strings ending with "rose", case-insensitively.
Use rlike() – it supports full regular expressions and can handle complex patterns.
No. ilike() is available only in Spark 3.0 and above.
Conclusion
In this article, I have explained the concept of PySpark like()vs rlike()vs ilike() for filtering DataFrame rows based on string patterns. Choosing the appropriate function is crucial when working with textual data, as each one handles pattern matching differently. While all three are used to match string patterns, they vary in syntax, pattern type (SQL-style vs. regex), and case sensitivity, making it important to select the one that best fits your specific use case.
Happy Learning!!
<a id="h-related-articles"></a>