pyspark.sql.functions.instr#

pyspark.sql.functions.instr(str, substr, start=None, occurrence=None)[source]#

Locate the position of the specified occurrence of substr column in the given string. Returns null if either of the arguments are null.

Added in version 1.5.0.

Changed in version 3.4.0: Supports Spark Connect.

Changed in version 4.3.0: Supports optional start and occurrence parameters.

Parameters:

strColumn or column name: target column to work on. A column that evaluates to a string.
substrColumn or literal string: substring to look for. A column that evaluates to a string.

Changed in version 4.0.0: substr now accepts column.
startint or Column, optional: Starting position (1-based, can be negative for backward search). If not specified, defaults to 1. A column that evaluates to an integer.
occurrenceint or Column, optional: Which occurrence to locate (must be > 0). Defaults to 1. A column that evaluates to an integer.

Returns:

Column: location of the substring as integer. Returns a column that evaluates to an integer.

See also

pyspark.sql.functions.locate()
pyspark.sql.functions.substr()
pyspark.sql.functions.substring()
pyspark.sql.functions.substring_index()

Notes

The position is not zero based, but 1 based index. Returns 0 if substr could not be found in str.

Examples

Example 1: Using a literal string as the ‘substring’

>>> from pyspark.sql import functions as sf
>>> df = spark.createDataFrame([("abcd",), ("xyz",)], ["s",])
>>> df.select("*", sf.instr(df.s, "b")).show()
+----+-----------+
|   s|instr(s, b)|
+----+-----------+
|abcd|          2|
| xyz|          0|
+----+-----------+

Example 2: Using a Column ‘substring’

>>> from pyspark.sql import functions as sf
>>> df = spark.createDataFrame([("abcd",), ("xyz",)], ["s",])
>>> df.select("*", sf.instr("s", sf.lit("abc").substr(0, 2))).show()
+----+---------------------------+
|   s|instr(s, substr(abc, 0, 2))|
+----+---------------------------+
|abcd|                          1|
| xyz|                          0|
+----+---------------------------+

Example 3: Using start and occurrence parameters

>>> from pyspark.sql import functions as sf
>>> df = spark.createDataFrame([("aabcd",), ("xyz",)], ["s",])
>>> df.select("*", sf.instr("s", "b", 1, 2)).show()
+-----+-----------------+
|    s|instr(s, b, 1, 2)|
+-----+-----------------+
|aabcd|                0|
|  xyz|                0|
+-----+-----------------+

Example 4: Using start parameter

>>> from pyspark.sql import functions as sf
>>> df = spark.createDataFrame([("aabcd",), ("xyz",)], ["s",])
>>> df.select("*", sf.instr("s", "a", 2)).show()
+-----+-----------------+
|    s|instr(s, a, 2, 1)|
+-----+-----------------+
|aabcd|                2|
|  xyz|                0|
+-----+-----------------+