Strings and patterns

Clean catalog names, extract SKU parts, and filter product-name prefixes.

Step 1 of 6 · Learn

Clean product names

The setup pads product names with spaces and builds a SKU reference from product_id. trim removes surrounding spaces; lower then normalizes case. Internal spaces remain, so Wireless keyboard stays two words. The original catalog is unchanged.

Lesson reference: PySpark and Scala

Clean product names

PySpark example

from pyspark.sql.functions import (
    col, lit, concat, trim, lower, upper, regexp_extract,
    format_string, to_date, to_timestamp, datediff
)

prepared_products = products.select(
    "product_id",
    concat(lit("  "), col("product_name"), lit("  ")).alias("raw_name"),
    concat(lit("SKU-"), col("product_id").cast("string")).alias("reference")
)

result = prepared_products.select(
    col("product_id"), lower(trim(col("raw_name"))).alias("clean_name")
).orderBy("product_id")
result.show()

Scala example

import org.apache.spark.sql.functions._

val prepared_products = products.select(
  col("product_id"),
  concat(lit("  "), col("product_name"), lit("  ")).alias("raw_name"),
  concat(lit("SKU-"), col("product_id").cast("string")).alias("reference")
)

val result = prepared_products.select(
    col("product_id"), lower(trim(col("raw_name"))).alias("clean_name")
).orderBy("product_id")
result.show()

Extract a SKU

regexp_extract uses a regular expression and a capture-group number. Group 1 is the SKU prefix; group 2 is the numeric suffix. The extracted value is text, not a number. An unmatched pattern returns an empty string.

PySpark example

from pyspark.sql.functions import (
    col, lit, concat, trim, lower, upper, regexp_extract,
    format_string, to_date, to_timestamp, datediff
)

prepared_products = products.select(
    "product_id",
    concat(lit("  "), col("product_name"), lit("  ")).alias("raw_name"),
    concat(lit("SKU-"), col("product_id").cast("string")).alias("reference")
)

result = prepared_products.select(
    col("product_id"),
    regexp_extract(col("reference"), "([A-Z]+)-([0-9]+)", 1).alias("reference_part")
).orderBy("product_id")
result.show()

Scala example

import org.apache.spark.sql.functions._

val prepared_products = products.select(
  col("product_id"),
  concat(lit("  "), col("product_name"), lit("  ")).alias("raw_name"),
  concat(lit("SKU-"), col("product_id").cast("string")).alias("reference")
)

val result = prepared_products.select(
    col("product_id"),
    regexp_extract(col("reference"), "([A-Z]+)-([0-9]+)", 1).alias("reference_part")
).orderBy("product_id")
result.show()

Match a name prefix

like uses SQL-style wildcards: % matches any sequence and _ matches one character. This example trims for comparison and selects names starting with L. The returned raw_name still has its original spaces. Matching is case-sensitive.

PySpark example

from pyspark.sql.functions import (
    col, lit, concat, trim, lower, upper, regexp_extract,
    format_string, to_date, to_timestamp, datediff
)

prepared_products = products.select(
    "product_id",
    concat(lit("  "), col("product_name"), lit("  ")).alias("raw_name"),
    concat(lit("SKU-"), col("product_id").cast("string")).alias("reference")
)

result = prepared_products.filter(
    trim(col("raw_name")).like("L%")
).select("product_id", "raw_name").orderBy("product_id")
result.show()

Scala example

import org.apache.spark.sql.functions._

val prepared_products = products.select(
  col("product_id"),
  concat(lit("  "), col("product_name"), lit("  ")).alias("raw_name"),
  concat(lit("SKU-"), col("product_id").cast("string")).alias("reference")
)

val result = prepared_products.filter(
    trim(col("raw_name")).like("L%")
).select("product_id", "raw_name").orderBy("product_id")
result.show()

example.pyPySpark

from pyspark.sql.functions import ( col, lit, concat, trim, lower, upper, regexp_extract, format_string, to_date, to_timestamp, datediff ) prepared_products = products.select( "product_id", concat(lit(" "), col("product_name"), lit(" ")).alias("raw_name"), concat(lit("SKU-"), col("product_id").cast("string")).alias("reference") ) result = prepared_products.select( col("product_id"), lower(trim(col("raw_name"))).alias("clean_name") ).orderBy("product_id") result.show()

1. Prepare prepared_productsPad names and build SKU references without changing products.

2. Clean product namesApply the displayed expression to the prepared input.

3. Inspect the resultCheck spaces, letter case, and the extracted text.

Source prepared_products6 rows

product_id	raw_name	reference
201	Wireless keyboard	SKU-201
202	Laptop stand	SKU-202
203	Desk lamp	SKU-203
204	USB-C hub	SKU-204
205	Travel backpack	SKU-205
206	Notebook set	SKU-206

Clean product names

Result6 rows

product_id	clean_name
201	wireless keyboard
202	laptop stand
203	desk lamp
204	usb-c hub
205	travel backpack
206	notebook set

Compare the source columns with the transformed result.

The example is loaded in the editor. Run it as written, then try a small change.

Runs on the Spark backend. First startup may take a moment.

Run your code to see the result.