Remove characters and format the remaining string 2

[slobad23]

I am using MS SQL server 2008. I want to create a calculated field which would change one value to another:

IMPORT
6.745,00 €

CALC
6,745.00

Is there any way I could perform this kind of calculation on the IMPORT field and be provided with the CALC value as a result?

Thanks in advance!

 

[starsky51]

Assuming the format of the imported values is consistent, the following example will convert them:

DECLARE @impval varchar(100)
SET @impval = '6.745,05 €'
SELECT CAST(REPLACE(LEFT(@impval, LEN(@impval) - 2), '.', ',') AS money) / 100

This:
1. Chops off the ' €' from the end of the string. ('6.745,05')
2. Replaces any '.' characters with ',' ('6,745,05')
3. Converts it to a money data type, removing the commas in the process (674505)
4. Divides by 100 to account for the two decimal places (6745.05)

Hope this helps.
Dave.

 

[slobad23]

Worked like a charm!

Thank you for the script and the explanation.