How to format integer in Spark SQL?

Question

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.

spark.apache.org/docs/2.3.0/api/sql/#format_string

Noixes
– Noixes

2020-08-19 05:57:35 +00:00
Commented Aug 19, 2020 at 5:57 — Noixes
– Noixes, Commented Aug 19, 2020 at 5:57

Daeho Ro · Accepted Answer · 2020-08-19 05:28:12Z

1

Use lpad function.

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

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

You can change '1' to id.

answered Aug 19, 2020 at 5:28

Daeho Ro

13.7k4 gold badges25 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to format integer in Spark SQL?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related