Casting a String (containing number in EU format) ...

Harsha777 · ‎08-26-2024

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!!

filipniziol · ‎08-26-2024

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()

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