how to get right substring using sql in spark 2.0

apache-spark

Solution

I see some people said should refer to the HQL document, then I try substring with negative argument, it works. This is simple but the reason that makes things complex is spark sql has no documentation. I do not think it's a good idea, it' not good for many people who want to use spark sql.

scala> val df = spark.sql("select a, substring(a,-2) as v from cdr");
df: org.apache.spark.sql.DataFrame = [a: string, v: string]

scala> df.show()
+-----------+---+
|a          |  v|
+-----------+---+
|      4.531| 31|
|      4.531| 31|
|      1.531| 31|
|      1.531| 31|
|      1.531| 31|
|      1.531| 31|
|      1.531| 31|
|      3.531| 31|
|      1.531| 31|
|      1.531| 31|
|      1.531| 31|
|      1.431| 31|
|      1.531| 31|
|      1.633| 33|
|      1.531| 31|
|      3.531| 31|
|      1.531| 31|
|      3.531| 31|
|      1.531| 31|
|      4.531| 31|
+-----------+---+
only showing top 20 rows

Problem

e.g. If I have a string column value like "2.450", I want to get right 2 characters "50" from this column, how to get it using sql from spark 2.0.1 I am running my sql on view created from dataframe ``` mydf.createOrReplaceTempView("myview"); ```

Original source