CODEWITHSUNDEEP
javaapache-sparkhadoopapache-spark-sqlamazon-emr
Ammu
Ammu

Reputation: 1

How to format integer in Spark SQL?

I am new to Spark. Any help will be appreciated. Is there any alternative for sql's FORMAT() in Spark SQL. My core logic is written as SQL and running with spark.sql("query"). I have a requirement to convert the id to 4 digit.

For example, if if is 1, it should be converted to 0001, if it is 12, then 0012. I know in SQL, we can do it as FORMAT("%04d", id) as id. But this is giving me error in Spark SQL saying FORMAT is not a function registered. Found format_number and format_string in Spark's documentation, but not helping in my case.

Note: I don't want to do this in my java code, but would like to do in SQL query itself.

Upvotes: 0

Views: 851

Answers (1)

Lamanus
Lamanus

Reputation: 13581

Use lpad function.

spark.sql("SELECT lpad('1', 4, '0')").show

+-------------+
|lpad(1, 4, 0)|
+-------------+
|         0001|
+-------------+

You can change '1' to id.

Upvotes: 1

Related Questions