String functions

Spark tutorial · PySpark.in

String Functions: lower, upper, trim, substring, instr, length

Overview: Text data is ubiquitous, and PySpark provides many functions to manipulate strings. We will look at:

lower & upper – convert text to lowercase or uppercase.
trim – remove leading and trailing whitespace.
substring – extract a substring from a string by position.
instr – find the position of a substring within a string.
length – count the number of characters in a string.

These functions help in cleaning and standardizing data (e.g., making case consistent, trimming unwanted spaces) and extracting parts of strings (like prefixes, area codes, etc.).

Typical Use Cases:

Cleaning data: making all text lower-case for uniformity (e.g., for case-insensitive comparisons), or trimming spaces that often come from user input or CSV files.
Extracting fixed-position codes: e.g., taking the first 3 characters of a product code, or the first 2 letters of a state abbreviation.
Searching within strings: finding if a substring exists and where (using instr or the contains method which is similar for checking existence).
Measuring string lengths: perhaps to validate if an ID has the correct number of characters or to filter out unusually long names/descriptions.

Let's create a small DataFrame of customer names to illustrate these functions. We'll intentionally include inconsistent casing and extra spaces:

```

from pyspark.sql import SparkSession

spark = SparkSession.builder.appName("BuiltInFuncDemo").getOrCreate()

names_data = [

(" Alice Smith ",),

("BOB JONES",),

("Charlie",),

(" diana prince ",)

]

df_names = spark.createDataFrame(names_data, ["name"])

df_names.show(truncate=False)

```

(We disable truncation in show for clarity here.) We have four entries:

" Alice Smith " (leading and trailing spaces, mixed case).
"BOB JONES" (all uppercase, no extra spaces).
"Charlie" (title case, no extra spaces).
" diana prince " (leading space, trailing double space, all lowercase).

Now let's apply various string functions:

Lowercase and Uppercase

The functions lower(col) and upper(col) do exactly what their names suggest: convert all characters to lowercase or uppercase, respectively.

Example: Standardize names to all lower-case and all upper-case.

```

from pyspark.sql.functions import lower, upper,col

from pyspark.sql import SparkSession

spark = SparkSession.builder.appName("BuiltInFuncDemo").getOrCreate()

names_data = [

(" Alice Smith ",),

("BOB JONES",),

("Charlie",),

(" diana prince ",)

]

df_names = spark.createDataFrame(names_data, ["name"])

df_names.select(

col("name"),

lower(col("name")).alias("name_lower"),

upper(col("name")).alias("name_upper")

).show(truncate=False)

```

We can see:

"Alice Smith" became "alice smith" in lower-case and "ALICE SMITH" in upper-case, retaining the spaces.
"BOB JONES" lower-case is "bob jones", upper-case remains "BOB JONES" (no change since it was already upper-case).
"Charlie" -> "charlie"/"CHARLIE".
"diana prince" (with leading/trailing spaces) -> "diana prince"/"DIANA PRINCE" (the content's case changed, spaces unaffected).

Note: Lowercase/uppercase functions do not trim spaces or affect non-letter characters, they only change the case of letters. Leading/trailing spaces remain, as we saw (they appear before/after the names in the output with the same spacing). If you want to ignore spaces when changing case, you'd need to trim first (or after; but trimming after upper/lower might remove spaces you wanted to keep between words, so usually trim first if you're cleaning data).

Trimming Whitespace

The trim(col) function removes both leading and trailing whitespace from the string. This is very useful for cleaning up data where users or systems accidentally include spaces at the beginning or end of strings.

Example: Trim whitespace from names.

```

from pyspark.sql.functions import trim,col

from pyspark.sql import SparkSession

spark = SparkSession.builder.appName("BuiltInFuncDemo").getOrCreate()

names_data = [

(" Alice Smith ",),

("BOB JONES",),

("Charlie",),

(" diana prince ",)

]

df_names = spark.createDataFrame(names_data, ["name"])

df_names.select(

col("name"),

trim(col("name")).alias("name_trimmed")

).show(truncate=False)

```

Now:

" Alice Smith " became "Alice Smith" with no leading/trailing spaces.
"BOB JONES" was unchanged (no leading/trailing spaces to remove).
"Charlie" unchanged.
" diana prince " became "diana prince" (leading and trailing spaces removed).

Notice in the output that for "Alice Smith", the trimmed version has no spaces at ends, and for "diana prince" similarly. Trimming did not change the internal spacing (it didn't remove the space between "diana" and "prince", only the ones at the very ends).

Trimming is often the first step in cleaning string columns – it's common to trim and then apply lower/upper or other transformations.

Substring Extraction

The substring(col, pos, len) function extracts a portion of the string starting at position pos (1-indexed, meaning 1 is the first character) with a length of len characters. If the string is shorter than the desired slice, it will return whatever is available (or empty string if pos is beyond string length).

Example: Extract the first 5 characters of each name.

```

from pyspark.sql.functions import substring,col

from pyspark.sql.functions import length

from pyspark.sql import SparkSession

spark = SparkSession.builder.appName("BuiltInFuncDemo").getOrCreate()

names_data = [

(" Alice Smith ",),

("BOB JONES",),

("Charlie",),

(" diana prince ",)

]

df_names = spark.createDataFrame(names_data, ["name"])

df_names.select(

col("name"),

substring(col("name"), 1, 5).alias("first5_chars")

).show(truncate=False)

```

Let's interpret the results:

For " Alice Smith ": The first character (position 1) is a space (because of the leading space), and then "Alic". So we see " Alic" where the first character is still a space, followed by "Alic". If we had trimmed first, we would have gotten "Alice" as the first 5 letters. This demonstrates that substring is position-based including spaces.
"BOB JONES": Characters 1-5 are "BOB J" (it includes the space after "BOB").
"Charlie": First 5 are "Charl".
" diana prince ": First char is space, then "dian". So " dian" (one leading space then "dian").

If we wanted, for example, just the first name of each (assuming the first name ends at the first space), a more robust approach would be to use split or instr (find space and take substring up to that). Here, our goal was just to show how substring works.

Note: Remember that pos is 1-based. If you put 0 or 1 incorrectly, you might get unexpected results or empty strings. For example, substring(col, 0, 5) might not return anything because Spark expects positions starting at 1. In our code, we used 1.

Finding Substring Position with instr

The instr(str, substring) function searches for the first occurrence of the substring in the given string and returns the 1-based index of where it occurs. If the substring isn't found, it returns 0. This is useful for parsing strings when you need to know positions (for example, finding the position of a delimiter).

Example: Find the position of the first space in each name.

We will use instr(col("name"), " ") to find the first space character in the string.

```

from pyspark.sql.functions import instr

from pyspark.sql.functions import length, col

from pyspark.sql import SparkSession

spark = SparkSession.builder.appName("BuiltInFuncDemo").getOrCreate()

names_data = [

(" Alice Smith ",),

("BOB JONES",),

("Charlie",),

(" diana prince ",)

]

df_names = spark.createDataFrame(names_data, ["name"])

df_names.select(

col("name"),

instr(col("name"), " ").alias("first_space_index")

).show(truncate=False)

```

Interpretation:

"Alice Smith" (with leading space) – the first space is at position 1 (the very first character is a space).
"BOB JONES" – the first space is at position 4 (after "BOB").
"Charlie" – no space at all, so result is 0 (not found).
"diana prince" (leading space) – first space at position 1 again (the leading space, it finds that before the space between "diana" and "prince").

If we wanted to get the first name, we could use this position in a substring: e.g., substring(name, 1, instr(name, " ") - 1) would give everything up to just before the first space. (One would have to handle the case when instr returns 0 for strings with no space; a conditional or using when could handle that.)

Note: The instr function is case-sensitive by default and returns the first occurrence. For more complex searching (like regex or last occurrence), other functions or methods (like rlike for regex matching, or substring_index for splitting by a delimiter) might be used. But instr is fast and straightforward for simple substring search.

Length of Strings

The length(col) function returns the number of characters in the string (including spaces). This can be useful for validation or just understanding data (e.g., max length of a text field).

Example: Compare name length before and after trimming.

Let's show the length of the raw name vs the trimmed name to see the difference:

```

from pyspark.sql.functions import length,col

from pyspark.sql import SparkSession

spark = SparkSession.builder.appName("BuiltInFuncDemo").getOrCreate()

names_data = [

(" Alice Smith ",),

("BOB JONES",),

("Charlie",),

(" diana prince ",)

]

df_names = spark.createDataFrame(names_data, ["name"])

df_names.select(

col("name"),

length(col("name")).alias("len_before"),

length(trim(col("name"))).alias("len_after_trim")

).show(truncate=False)

```

Here we see, for example, "Alice Smith" had length 14 including the spaces, but after trim it's 11 (the letters plus the internal space). "diana prince" was 15 with spaces, 12 after trim. This confirms the number of spaces removed.

The length function counts characters, and since Spark strings are usually UTF-8, this counts bytes for multi-byte characters appropriately as well. (All our examples are simple ASCII letters and spaces.)

Best Practices for String Functions:

Converting case (lower/upper) is often used to normalize data for case-insensitive matching (e.g., comparing emails or codes in a uniform way). Keep in mind that these functions do not alter any other aspect of the string.
Trimming (trim) is usually one of the first cleaning steps for free-text data. Spark also provides ltrim and rtrim if you only want to trim one side, but trim (both sides) is most common.
When using substring, remember indexing starts at 1. Also, consider using substring_index (a function not covered above) if you need to split by a delimiter. For instance, substring_index(col, " ", 1) would give you the part of the string before the first space, and a negative index can give from the end.
instr is great for simple searches. If you just need a boolean whether a substring exists, you can also use col("name").contains("sub") which returns a boolean Column (this is equivalent to instr(...) > 0 under the hood).
The length function can be used for data validation (e.g., ensuring codes are of expected length) or filtering (e.g., filter(length(col("comment")) > 100) to get long comments).
All these string functions operate on each value in the column efficiently in the JVM; avoid using Python's string methods in a UDF for these purposes, as the built-in functions are much faster and can be optimized by Spark.

In practice, you will often use a combination of these. For example, to standardize a country code column, you might do trim(upper(col("country_code"))) to both trim and uppercase the code. Or to extract a portion of a code that is always the 1st to 3rd characters: substring(col("code"), 1, 3).

Using these functions, one can clean up messy text data (remove unwanted spaces, fix casing issues) and extract meaningful information from strings for further analysis.

String functions

String Functions: lower, upper, trim, substring, instr, length

More Spark tutorials