Casting string type column percentage to a decimal
Solution 1:
You are referencing column name as "sales_%" which is interpreted as literal string by Spark. You need to use back-ticks instead of quotes. Also, there is no need cast to integer before division by 100. Try this:
spark.sql("""
select date,
cast(replace(`sales_%`,'%','')/100 as decimal(2,2)) as sales
from table_name
""").show()
#+----------+-----+
#| date|sales|
#+----------+-----+
#|20/12/2021| 0.50|
#|21/12/2021| 0.29|
#|22/12/2021| 0.60|
#|23/12/2021| 0.12|
#|24/12/2021| 0.75|
#|25/12/2021| 0.28|
#+----------+-----+