use length function in substring in spark

Question

I'm using spark 2.1.

Using a length function inside a substring for a Dataframe is giving me an error (mismatch).

val SSDF = testDF.withColumn("newcol", substring($"col", 1, length($"col")-1))

Answer 1

You can use the function expr

val data = List("..", "...", "...")
val df = sparkContext.parallelize(data).toDF("value")
val result = df.withColumn("cutted", expr("substring(value, 1, length(value)-1)"))
result.show(false)

This might help

Comments

can you provide some working examples????

---

Only the last column is shown by this method

Answer 2

You can try this

val substrDF =testDF.withColumn("newcol", $"col".substr(lit(1), length($"col")-1))

Comments

thank you so much !!

Answer 3

You have passed the wrong parameters. Here is the right syntax:

substring(str: Column, pos: Int, len: Int): Column 

Answer 4

You can also use UDF

testDF.withColumn("newcol", regexp_replace($"name", ".$" , "") ).show