How to remove specific character from string in spark-sql

Question

I want to convert '123,456' to 123456 (string to int) by spark-sql.

I tried like this.

REPLACE('123,456', ',', '')

But it didn't work, because spark-sql don't permit REPLACE()

Answer 1

replace works as expected for me (Spark 2.3.0):

spark.sql("select int(replace('123,456', ',', '')) result").collect()
# [Row(result=123456)]

Answer 2

assuming all data are numeric while stored in varchar convert function should solve your issue

conv(Column num,
          int fromBase,
          int toBase)