I have column called revenue it is string column and it holds currency data.
e.g Revenue: R 12 000.00, R 120 000.00, R 12 000 000.00
Where R is my currency sign( SA Rand).
I have tried all possible answers so far and is not working.
Query: Select Revenue, Case when isnumeric(Revenue)=1(true) Then revenue else CONVERT(DECIMAL(28,4),Replace(Replace(Revenue, 'R',''),' ','') end
from my Table1
Though I get the following error.
Msg 8114, Level 16, State 5, Line 1
Error converting data type nvarchar to numeric.