Replacing regex pattern with another string works, but replacing with NONE replaces all values

Question

I am trying to replace all strings in a column that start with 'DEL_' with a NULL value.

I have tried this:

customer_details = customer_details.withColumn("phone_number", F.regexp_replace("phone_number", "DEL_.*", ""))

Which works as expected and the new column now looks like this:

+--------------+
|  phone_number|
+--------------+
|00971585059437|
|00971559274811|
|00971559274811|
|              |
|00918472847271|
|              |
+--------------+

However, if I change the code to:

customer_details = customer_details.withColumn("phone_number", F.regexp_replace("phone_number", "DEL_.*", None))

This now replaces all values in the column:

+------------+
|phone_number|
+------------+
|        null|
|        null|
|        null|
|        null|
|        null|
|        null|
+------------+

Regex replacement is only possible with string data only. null is not a string type. — Wiktor Stribiżew
– Wiktor Stribiżew, Commented Jul 23, 2020 at 10:59

Som · Accepted Answer · 2020-07-23 11:27:13Z

3

Try this-

scala

df.withColumn("phone_number", when(col("phone_number").rlike("^DEL_.*"), null)
          .otherwise(col("phone_number"))
      )

python

df.withColumn("phone_number", when(col("phone_number").rlike("^DEL_.*"), None)
          .otherwise(col("phone_number"))
      )

Update

Query-

Can you explain why my original solution doesn't work? customer_details.withColumn("phone_number", F.regexp_replace("phone_number", "DEL_.*", None))

Ans- All the ternary expressions(functions taking 3 arguments) are all null-safe. That means if spark finds any of the arguments null, it will indeed return null without any actual processing (eg. pattern matching for regexp_replace). you may wanted to look at this piece of spark repo

  override def eval(input: InternalRow): Any = {
    val exprs = children
    val value1 = exprs(0).eval(input)
    if (value1 != null) {
      val value2 = exprs(1).eval(input)
      if (value2 != null) {
        val value3 = exprs(2).eval(input)
        if (value3 != null) {
          return nullSafeEval(value1, value2, value3)
        }
      }
    }
    null
  }

edited Jul 23, 2020 at 11:27

answered Jul 23, 2020 at 10:43

Som

6,3681 gold badge13 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

KOB Over a year ago

This works, thanks. Can you explain why my original solution doesn't work?

Collectives™ on Stack Overflow

Replacing regex pattern with another string works, but replacing with NONE replaces all values

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related