Everyone knows 3 min read · Strings 1 practice problem ↓

String functions

concat vs concat_ws, substring, split, trim and padding, with the null traps in each.

On this page

Show code in

Every code block on the page follows this.

You will learn

The difference between concat and concat_ws, and the null trap
How to cut strings with substring and split
How to clean text with trim, lower, upper, initcap and lpad
Which string functions are 1-based and which are 0-based

Read first

select · 4 min read

Comfortable with these? Read on.

TL;DR Spark has a built-in function for almost every string job, and built-ins are much faster than Python UDFs. Remember three rules: concat returns null if any input is null, while concat_ws skips nulls; substring starts counting at 1; and split takes a regular expression.

concat vs concat_ws

Function	With a null input	Example
`concat(a, b, ...)`	The whole result is null	`concat("Bo", " ", null)` → null
`concat_ws(sep, a, b, ...)`	The null is skipped	`concat_ws(" ", "Bo", null)` → "Bo"

For names, addresses and keys built from several columns, concat_ws is almost always what you want. If you build a join key with concat, rows with any null part silently stop matching.

Run the example

PySparkSpark SQL

from pyspark.sql import functions as F
result = customers.select(
    "id",
    F.concat("first", F.lit(" "), "last").alias("with_concat"),
    F.concat_ws(" ", F.initcap(F.trim("first")), "last").alias("full_name"),
    F.upper(F.substring("city", 1, 3)).alias("city_code"),
    F.split("phone", "-").alias("phone_parts"),
)

SELECT id,
       concat(first, ' ', last)                    AS with_concat,
       concat_ws(' ', initcap(trim(first)), last) AS full_name,
       upper(substring(city, 1, 3))               AS city_code,
       split(phone, '-')                          AS phone_parts
FROM customers

Switch to PySpark to edit and run this example in your browser.

Row 2 shows the null trap: with_concat is null because last is null, while full_name is "Bo". Row 1 shows why cleaning comes first: the raw value " ana " keeps its spaces inside with_concat.

The toolkit

Job	Function	Notes
Remove spaces	`trim`, `ltrim`, `rtrim`	Removes spaces only; tabs and non-breaking spaces need `regexp_replace(c, r"^\s+\|\s+$", "")`
Change case	`lower`, `upper`, `initcap`	Lower-case both sides before comparing or joining on text
Cut	`substring(c, pos, len)`	1-based. A negative pos counts from the end: `substring(c, -4, 4)` is the last 4 characters
Split	`split(c, pattern)`	The pattern is a regex: split on a dot with `"\\."`, not `"."`
Pick a piece	`split(c, "-")[0]` or `F.split(c, "-").getItem(0)`	0-based, unlike substring
Length	`length`	Characters, not bytes
Pad	`lpad(c, 5, "0")`, `rpad`	Zero-pad codes; also truncates values longer than the width
Replace	`replace` (literal, Spark 3.5+), `regexp_replace`, `translate`	`translate(c, "-.", "")` deletes characters one by one
Find	`instr`, `locate`, `contains`, `startswith`, `like`	instr and locate return 1-based positions, 0 if not found

1-based or 0-based?

SQL functions count from 1: substring, instr, locate, element_at. Array indexing with brackets or getItem counts from 0. split("98-765-43", "-")[0] is "98", while element_at(split(...), 1) is also "98".

Cleaning text before a join

Text keys from two systems rarely match exactly. Normalise both sides the same way before joining: F.lower(F.trim(c)), and remove punctuation with regexp_replace when needed. A join on raw names silently drops "Pune" vs "pune " pairs.

Why this matters: every built-in function runs inside the JVM on whole batches of rows, and Catalyst can optimise around it. A Python UDF that does the same .strip().lower() ships every row to a Python process and back, typically several times slower.

Common mistakes

Building keys with concat

Any null part makes the whole key null. Use concat_ws, or coalesce each part first.

split on "." or "|"

Both are regex metacharacters. Escape them: "\\." and "\\|".

Off-by-one between substring and split

substring is 1-based, array indexing is 0-based.

Joining on uncleaned text

Trailing spaces and case differences make rows fail to match. Trim and lower both sides.

Key takeaways

concat returns null if any part is null; concat_ws skips nulls.
substring is 1-based; array indexing is 0-based.
split takes a regex, so escape dots and pipes.
Normalise text with trim and lower before comparing or joining.

Check yourself

3 questions

1. What is concat("a", null, "b")?

Show the answer

null. concat returns null when any input is null.

2. What does substring("Mumbai", 1, 3) return?

Show the answer

"Mum". substring is 1-based: start at the first character and take 3.

3. Why does split("a.b.c", ".") not split on dots?

Show the answer

The pattern is a regex and "." matches any character. Escape it as "\\." to split on a literal dot.

Practice it

Interview problems that use this: write the PySpark, run it, and get graded on hidden tests.

Solve: Word Count →

Word Count Medium

Keep going

Up next · lesson 7 of 35 · 3 min read
Parsing dates and timestamps
to_date, to_timestamp and date_format patterns, and why yyyy and YYYY are not the same.

Previous: cast and schemas

Primary sources: String functions · functions.concat_ws