format and tonumber

[DanHD]

Hello

As a result from a query I get ‘EUR 11,00’ or ‘USD 123,96’ or ‘DEM 110,00’ and so on. Always the currency with 3 characters and the amount. What I need is only the amount as numbers because I need it in another formula. As example results ’11,00’ or ‘123,96’ or ‘110,00’ and so on.
How can I do this?


Dan

 

[bborissov]

SELECT CAST(SUBSTRING(YourField, 4,8000) AS Numeric(10,2))
FROM YourTable

NOT TESTED AT ALL!

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[markros]

Boris,

If the amounts have commas, the conversion is going to fail. You first need to replace commas to nothing. Also, to be on the safe side, you may want to search for first digit instead of assuming that the currency description is always 3 characters.

PluralSight Learning Library

 

[gmmastros]

The OP said that the currency symbol will always be 3 characters. Also, some countries use commas in their numbers the way we use decimal points. If I am not mistaken, this is controlled by the language setting of the login connected to SQL.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

Thanks - I could not read properly at the end of the day.

So, how exactly will we convert the number with commas (and figure out if commas are decimals separator or thousands separator)?

PluralSight Learning Library

 

[DanHD]

hello all

we use indeed comma's (netherlands) and it gives an error:
"Msg 8114, Level 16, State 5, Line 1
Error converting data type nvarchar to numeric."
So how do I convert this in the formula, I can't change the settings on sql level, because this is not allowed.


Dan

 

[DanHD]

I think I've it:

SELECT cast(SUBSTRING((replace (YourField, ',' , '.')), 4,8000)as numeric (10,2))*100

it works in mssql, but do I forget something?

Dan