splitting a string column in spark sql based on scenario

user_13992906 0
Viewed 196

A particular Column pattern is like this

10-Apple
11-Mango
Orange
78-Pineapple
45-Grape

And I want to make two columns out of it

col1  col2
10    Apple
11    Mango
null  Orange
78    Pineapple
45    Grape

When I split the below string

SELECT split("10-Apple",'-',2)

It gives me ["10","Apple"] which is correct

But when I split below string which has no delimiter(-)

SELECT split("Orange",'-',2)

It gives me ["Orange"]

How can I have null instead of second element in the first place.

Like this [null,"Orange"]

apache-spark-sql apache-spark sql
1 Answers

Use regexp_extract

df.withColumn("number", regexp_extract($"column", "(\\d+)?\\-?(.*)", 1))
  .withColumn("fruit", regexp_extract($"column", "(\\d+)?\\-?(.*)", 2))

Regex means

  • (\\d+)? First capturing group, any number may be present or not
  • \\-? Literal dash, again may be present or not
  • (.*) any number of characters after the previous characters

OUTPUT

+------------+------+---------+
|      column|number|    fruit|
+------------+------+---------+
|    10-Apple|    10|    Apple|
|    11-Mango|    11|    Mango|
|      Orange|      |   Orange|
|78-Pineapple|    78|Pineapple|
|    45-Grape|    45|    Grape|
+------------+------+---------+

EDIT

Using Spark SQL from your comment

scala> spark.sql("""select distinct column, regexp_extract(column, "(\\d+)?\\-?(.*)", 1) as number, regexp_extract(column, "(\\d+)?\\-?(.*)", 2) as fruit from table""").show
+------------+------+---------+
|      column|number|    fruit|
+------------+------+---------+
|    10-Apple|    10|    Apple|
|    11-Mango|    11|    Mango|
|    45-Grape|    45|    Grape|
|      Orange|      |   Orange|
|78-Pineapple|    78|Pineapple|
+------------+------+---------+
user_63783110
Related