How to use split function with variable delimiter for each row?

user_13710576 0
Viewed 374
Input DF:
+-------------------+---------+
|VALUES             |Delimiter|
+-------------------+---------+
|50000.0#0#0#       |#        |
|0@1000.0@          |@        |
|1$                 |$        |
|1000.00^Test_string|^        |
+-------------------+---------+

Expected Output DF:
+-------------------+---------+----------------------+
|VALUES             |Delimiter|SPLITED_VALUES        |
+-------------------+---------+----------------------+
|50000.0#0#0#       |#        |[50000.0, 0, 0]       |
|0@1000.0@          |@        |[0, 1000.0]           |
|1$                 |$        |[1]                   |
|1000.00^Test_string|^        |[1000.00, Test_string]|
+-------------------+---------+----------------------+

Code:

import sparkSession.sqlContext.implicits._
val dept = Seq(("50000.0#0#0#", "#"),("0@1000.0@", "@"),("1$", "$"),("1000.00^Test_string", "^")).toDF("VALUES", "Delimiter")

I am very new to spark, trying to split value of "VALUES" column by using Delimiter which is in another column.

Tried to use spark split function as

val dept2 = dept.withColumn("SPLITED_VALUES", split(col("VALUES"), "#"))

but here split function take delimiter as constant value, i am not able to pass this as

val dept2 = dept.withColumn("SPLITED_VALUES", split(col("VALUES"), col("Delimiter")))

Can anyone suggest better approach for this?

apache-spark-sql apache-spark
1 Answers

Check below code.

scala> df
.withColumn("delimiter",concat(lit("\\"),$"delimiter"))
.withColumn("split_values",expr("split(values,delimiter)"))
.show(false)
+-------------------+---------+----------------------+
|values             |delimiter|split_value           |
+-------------------+---------+----------------------+
|50000.0#0#0#       |\#       |[50000.0, 0, 0, ]     |
|0@1000.0@          |\@       |[0, 1000.0, ]         |
|1$                 |\$       |[1, ]                 |
|1000.00^Test_string|\^       |[1000.00, Test_string]|
+-------------------+---------+----------------------+

Updated

scala> df
.withColumn("delimiter",concat(lit("\\"),$"delimiter"))
.withColumn("data",expr("array_remove(split(trim(values),delimiter),'')"))
.show(false)

+-------------------+---------+----------------------+
|values             |delimiter|data                  |
+-------------------+---------+----------------------+
|50000.0#0#0#       |\#       |[50000.0, 0, 0]       |
|0@1000.0@          |\@       |[0, 1000.0]           |
|1$                 |\$       |[1]                   |
|1000.00^Test_string|\^       |[1000.00, Test_string]|
+-------------------+---------+----------------------+
user_85934140
Related