how to truncate decimal places in databricks without rounding off in databricks?

user_10009190 0
Viewed 1117

select truncate(12.455555,2)

I was trying to truncate the decimal value in the database from databricks but it was giving me the following error. it gave the same error when I tried executing a simple statement for trimming the decimal places given above.

Error-

Error in SQL statement: AnalysisException: Undefined function: 'truncate'. This function is neither a registered temporary function nor a permanent function registered in the database 'default'.; line 1 pos 7

Can anyone tell me how we can truncate the decimal places without rounding off the decimals?

pyspark databricks apache-spark-sql sql
2 Answers

You can use substring.

spark.sql("select substring(12.455555,0, instr(12.455555,'.')+2) as out").show()

+-----+
|  out|
+-----+
|12.45|
+-----+
user_87733090

If you want to do that for printing the data, then you can use format_number function (doc), like this:

SELECT format_number(12332.123456, '#.###');
Alex_Ott_186270
Related