How to Search and Extract Text in PySpark Using String Functions

Top 6 essential functions to find, locate and pull exactly what you need from text columns. · 6 mins read

Photo by Sasun Bughdaryan on Unsplash

Have you faced any business situation where you need to display a domain name from an email address? Or extract the specific keyword from a long text column?

Searching and extracting from text is one of the most common tasks in data cleaning — and PySpark gives you a powerful set of functions to do it with precision.

In the previous blog, **How to Clean and Transform Text Columns in PySpark Using String Functions, we covered how to reshape and standardise text using upper(), lower(), initcap(), translate(), and overlay()** . Now we will take a step further. Instead of changing the entire string, we will find and extract the part which we need for further analysis.

I am saving this blog as my text search and extraction reference guide in PySpark. Whenever I need to locate or extract content from a string column on a project, this is where I come back to.

In this blog, you will learn:

1. instr()

2. locate()

3. substring()

4. substring_index()

5. regexp_extract()

6. ascii()

Your Coding Environment

Your Coding Environment Set up

Using the same Docker + SparkSession setup as the previous chapters. New to the series?

Get set up first: **Docker setup guide or Google Colab setup guide**

Initiate SparkSession

from pyspark.sql import SparkSession

spark = SparkSession.builder \
    .master("spark://spark-master:7077") \
    .appName("SearchExtraction") \
    .getOrCreate()

Sample Dataset

For this blog, we will use a customer dataset. It intentionally has the kind of data that requires searching and extracting specific parts, for example, email addresses, phone number formats, product codes embedded in notes, and raw text with keywords.

from pyspark.sql import Row
from pyspark.sql.functions import col

data = [
    Row(cust_id=1001, email="alice.johnson@gmail.com",   phone="+44-7911-123456", notes="Prefers contact via email. Ref: ORD-2024-001"),
    Row(cust_id=1002, email="bob.smith@yahoo.co.uk",     phone="+44-7922-234567", notes="Called on 12/03/2024. Ref: ORD-2024-002"),
    Row(cust_id=1003, email="clara.perez@outlook.com",   phone="+1-800-345678",   notes="Escalated complaint. Ref: ORD-2024-003"),
    Row(cust_id=1004, email="david.lee@company.org",     phone="+44-7944-456789", notes="VIP customer. No cold calls. Ref: ORD-2024-004"),
    Row(cust_id=1005, email="emma.watson@gmail.com",     phone="+1-900-567890",   notes="New sign-up via referral. Ref: ORD-2024-005"),
]

df = spark.createDataFrame(data)
df.show(truncate=False)

Image by author

In this dataset, you will observe the following issues.

1. For the email address column, the domain is always after the @ character, but its position changes row by row.
2. For the notes column — the order reference number ORD-2024-XXX is suppressed inside free text.

These are exactly the kinds of extraction challenges we are going to solve.

Import String Functions

Let’s import some of the PySpark packages for string functions.

from pyspark.sql.functions import instr, locate, substring, substring_index, regexp_extract, ascii, col, lit

instr() — find the position of a substring

Use Case: Before you can extract a part of a string, you often need to know where it starts. instr() tells you the position of the first occurrence of a substring inside a column.

df.select(
    "email",
    instr(col("email"), "@").alias("at_position")
).show(truncate=False)

image by author

How it works: instr(str, substr) returns the position of the first occurrence of substr inside str. Position index starts at 1 in PySpark, not 0. If the substring is not found, it returns 0.

Troubleshooting: If instr() returns 0, it means the substring was not found — not that it is at position 0. Always check for 0 before using the result in further calculations.

locate() — find a substring from a specific starting position

Use Case: The locate() function does the same job as instr(). But it gives you extra control - you can tell it where to start searching from. This is useful when a substring appears more than once, and you want to find the second or third occurrence.

# Find the position of the second "-" in phone number
df.select(
    "phone",
    locate("-", col("phone"), 4).alias("second_dash_pos")
).show(truncate=False)

image by author

How it works: locate(substr, str, pos) searches for substr inside str, but only from position pos onwards. This lets you skip past the first occurrence and find the next one.

Troubleshooting: Notice the argument order is different from instr(). In instr(), it is instr(str, substr) — column first. In locate(), it is locate(substr, str, pos) — substring first. This trips up a lot of people. Always double-check the order.

substring() — extract a fixed portion of a string

Use Case: When you know exactly where a value starts and how long it is, the substring() function is the cleanest way to extract it. For example, Country dialling codes, date portions, and fixed-width codes are perfect use cases.

# Extract the country code from the phone number (characters 1 to 3)
df.select(
    "phone",
    substring(col("phone"), 1, 3).alias("country_code")
).show(truncate=False)

image by author

How it works: The substring(str, pos, len) function extracts the length of the characters starting from position pos. Like all PySpark string functions, position starts at 1.

substring_index() — extract text before or after a delimiter

Use Case: Email addresses, file paths, and domain names all have a natural delimiter. substring_index() lets you split at that delimiter and keep exactly the part you want - without needing regex.

# Extract everything before "@" — the username part of the email
df.select(
    "email",
    substring_index(col("email"), "@", 1).alias("email_username")
).show(truncate=False)

image by author

How it works: The substring_index(str, delim, count) function works as follows. If the count is positive, it returns everything to the left of the nth delimiter. If the count is negative, it returns everything to the right of the nth delimiter from the end.

Troubleshooting: Many beginners reach for split() when they just need one side of a delimiter. substring_index() is cleaner and faster for this — split() is better when you need all the parts as an array.

regexp_extract() — extract a pattern using regex

Use Case: When a value follows a predictable pattern rather than being at a fixed position, regular expressions are the ideal tool. For example, order references, postcodes, dates, and product codes.

# Extract the order reference (e.g. ORD-2024-001) from the notes column
df.select(
    "notes",
    regexp_extract(col("notes"), r"ORD-\d{4}-\d{3}", 0).alias("order_ref")
).show(truncate=False)

image by author

How it works: regexp_extract(str, pattern, idx) — pattern is a Java regex string, and idx refers to the capture group to return. Use 0 to return the entire match. Use 1, 2, etc., to return specific groups defined with () in your pattern.

# Using a capture group to extract just the order number portion
df.select(
    "notes",
    regexp_extract(col("notes"), r"ORD-(\d{4})-(\d{3})", 1).alias("order_year"),
    regexp_extract(col("notes"), r"ORD-(\d{4})-(\d{3})", 2).alias("order_seq")
).show(truncate=False)

image by author

Troubleshooting: If the pattern is not found, regexp_extract() returns an empty string — not null and not 0.

ascii() — get the numeric value of the first character

Use Case: Though not typically used for daily cleaning, it is crucial for identifying invisible characters, non-printable bytes, or unexpected encoding issues at the start of a string.

df.select(
    "email",
    ascii(col("email")).alias("first_char_ascii")
).show(truncate=False)

image by author

How it works: ascii(col) returns the numeric ASCII value of the first character only of each string. “alice.johnson@gmail.com” starts with “a”, which is ASCII 97.

Download

Please find the full code in the GitHub folder.

**Chapter 10**

Conclusion

Text columns rarely give you exactly what you need in the exact format you need it. But with these 6 functions, you now have the tools to find anything inside a string and extract it cleanly — whether it sits at a fixed position, after a known delimiter, or follows a pattern hidden in free text.

If you found this useful, please like, share, and comment — it helps more learners discover the series. And if you haven’t already, subscribe to my YouTube channel for more content.

**Arpita’s Tech Corner — YouTube**

Let’s learn together.