Casting a String (containing number in EU format) to a Decimal

Harsha777 — Mon, 26 Aug 2024 09:24:52 GMT

Hi,

I have a string column containing a number in EU format, has comma instead of dot, e.g. 10,35

I need to convert this string into a proper decimal data type as part data transformation into the target table.

I could do it as below by replacing the "," in the string to "." and then using the cast function, col_name is string column with values like 3,35 10,4 5,86

cast(replace(col_name,',','.') as decimal(10,4))

But, is there any direct function which handles "," to "." (format) conversion internally and delivers the expected result?

Many thanks!!

Re: Casting a String (containing number in EU format) to a Decimal

filipniziol — Mon, 26 Aug 2024 12:28:18 GMT

Hi @Harsha777 ,

Your solution looks good!

However, you may try also to_number function, but unfortunately still will need to first to replace "," with ".".

from pyspark.sql.functions import to_number, regexp_replace, lit data = [("10,6523",), ("10,23",)] df = spark.createDataFrame(data, ["col_name"]) df = df.withColumn("decimal_col", to_number(regexp_replace("col_name", ",", "."), lit("9999999.9999"))) df.show()

Without replacement it will just remove the comma:

from pyspark.sql.functions import to_number, lit data = [("10,6523",), ("10,23",)] df = spark.createDataFrame(data, ["col_name"]) df = df.withColumn("decimal_col", to_number("col_name", lit("99,9999"))) df.show()

topic Casting a String (containing number in EU format) to a Decimal in Data Engineering

Casting a String (containing number in EU format) to a Decimal

Re: Casting a String (containing number in EU format) to a Decimal