Pyspark SQL ilike() Using Wildcard Example

The ilike() function in PySpark is used to filter rows based on case-insensitive pattern matching using wildcard characters, just like SQL’s ILIKE operator. This is particularly useful when you want to match strings without worrying about case sensitivity.

In this article, you'll learn how to use the ilike() function in PySpark to filter DataFrame rows using wildcard characters based on single or multiple conditions.

Key Points-

The ilike() function is used to filter DataFrame rows using case-insensitive pattern matching.
Works similar to SQL’s ILIKE clause.
Supports % to match zero or more characters.
Supports _ to match exactly one character.
Returns a boolean column for filtering.
Multiple ilike() conditions can be combined using | (OR) or & (AND).
Only available in PySpark 3.1.0+.
Only works with string columns.
Case-insensitive, unlike like() which is case-sensitive.
Returns an empty DataFrame if no matches are found.

PySpark ilike() Function

The ilike() function is used to compare a string column with a pattern, similar to like(), but it ignores case sensitivity. It supports wildcard characters such as _ (for a single character) and % (for multiple characters) to perform pattern matching and returns a boolean column indicating whether each row meets the condition.

ilike() Syntax

The following is the syntax of ilike().


# Syntax of the ilike() function
col("column_name").ilike("pattern")

Parameters

pattern (str): A string pattern using SQL-style wildcards:

% matches zero or more characters
_ matches a single character

Return Value

Returns a column of boolean values.

Let's create a PySpark DataFrame and explore how to use ilike() to perform case-insensitive filtering on a DataFrame.


# Create DataFrame and SparkSession
from pyspark.sql import SparkSession
spark = SparkSession.builder.appName("ilike-example").getOrCreate()

data = [
    (1, "James Smith"),
    (2, "Michael Rose"),
    (3, "Robert Williams"),
    (4, "Rames Rose"),
    (5, "rames rose"),
]

df = spark.createDataFrame(data, ["id", "name"])
df.show()

Yields below the output.

PySpark ilike() with Wildcards

You can use wildcard characters like _ and % with the ilike() function to filter rows in a DataFrame. Let's apply these wildcards to a specific column in the given DataFrame to filter rows based on a single-character (_) or multi-character (%) pattern matches.


# Case-insensitive match with '_'
df.filter(col("name").ilike("_A%")).show()

# Case-insensitive match with '%'
df.filter(col("name").ilike("%rose%")).show()

Yields below the output.

In the above examples, "_A%" matches any string where the second character is 'A' (regardless of case), followed by zero or more characters. Meanwhile, "%rose%" matches any string that contains the substring 'rose' anywhere in it, ignoring letter casing due to the use of ilike().

PySpark ilike() with Multiple Patterns (AND)

You can combine multiple ilike expressions using logical operators such as | (OR) and & (AND) to filter rows in a DataFrame. In this example, I will implement the multiple patterns using the ilike() function along with the AND (&) operator.


# Fiter rows by using multiple patterns
df.filter((col("name").ilike("rames%")) & (col("name").ilike("%rose"))).show()

# Output:
+---+----------+
| id|      name|
+---+----------+
|  4|Rames Rose|
|  5|Rames rose|
+---+----------+

In the above code, "rames%" matches any string that starts with 'rames' (case-insensitive), followed by any number of characters. Similarly, "%rose" matches any string that ends with 'rose', regardless of casing. The & operator is used to combine both conditions with a logical AND, so only rows that satisfy both patterns will be returned.

PySpark ilike() with Type Casting

The ilike() function operates only on string columns. To use it with a numeric column like id, you need to first cast the column to a string type. Once converted, you can apply ilike() to filter rows that match the specified pattern.


# Apply ilike() function to int column
df.filter(col("id").cast("string").ilike("1%")).show()

# Output:
+---+-----------+
| id|       name|
+---+-----------+
|  1|James Smith|
+---+-----------+

Get an Empty DataFrame using ilike()

When no rows match the given pattern, ilike() returns an empty DataFrame with the same schema.


# Get empty DataFrame using ilike()
df.filter(col("name").ilike("__a%")).show()

# Output:
+---+----+
| id|name|
+---+----+
+---+----+

like() vs ilike() vs rlike()

The table below highlights the differences between the like(), ilike(), and rlike() functions in PySpark.

| Function | Case-Sensitive | Wildcards/Regex | Best Use | | --- | --- | --- | --- | | like() | Yes | SQL-style (%, _) | Simple case-sensitive matches | | ilike() | No | SQL-style (%, _) | Simple case-insensitive matches | | rlike() | Yes (default) / (with (?i)) | Full regex | Advanced matching logic |

Frequently Asked Questions of PySpark like() Function

ilike() is a case-insensitive pattern matching function used to filter string columns in PySpark DataFrames.

The ilike() function is available from PySpark version 3.1.0 onward.

Unlike like(), which is case-sensitive, ilike() performs case-insensitive comparisons.

ilike() works only with string-type columns and will raise an error if applied to non-string columns.

You can combine multiple ilike() conditions using logical operators such as AND (&) and OR (|).

ilike() supports the % and _ wildcards for multi-character and single-character matches, respectively.

You can use the bitwise NOT operator (~) to negate the match condition and filter non-matching rows.

Conclusion

In this article, you have learned how to use the ilike() function in PySpark to perform case-insensitive string filtering using wildcard characters. With support for pattern matching using % and _, ilike() offers a powerful alternative to like() when dealing with text data where case should be ignored.

Happy Learning!!

References

https://spark.apache.org/docs/latest/api/python/reference/pyspark.sql/api/pyspark.sql.Column.ilike.html