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.
IsNumeric function is not that accurate.
You should use a custom function or
case when Replace(Replace(Revenue, ‘R’,’’),’ ‘,’’) NOT LIKE '%[^0-9]%' ...
You would also want to make sure, this is only done for the Rand currency, by filtering with the "where" statement:
where revenue like 'R %'
or you should handle other currency (if you have some).
Are you looking for something like this?
DECLARE @t table(ID int PRIMARY KEY,Revenue varchar(100));
INSERT @t ( ID , Revenue ) VALUES ( 1, 'R 12 000.00, R 120 000.00, R 12 000 000.00' );
WITH n1 AS (SELECT n FROM (VALUES(0),(1),(3),(4))t(n))
, n2 AS (SELECT n.n FROM n1 n CROSS JOIN n1)
, n3 AS (SELECT n.n FROM n2 n CROSS JOIN n2)
, n4 AS (SELECT n.n FROM n3 n CROSS JOIN n3)
, n5 AS (SELECT Row_Number() OVER (ORDER BY n) n FROM n4)
, Row_Number() OVER(PARTITION BY t.ID ORDER BY n.n) Pos
, Substring(LTrim(RTrim(Substring(Revenue, n.n, CharIndex(',', Revenue+',', n.n+1)-n.n))), 3, Len(t.Revenue)-3) Revenue
FROM @t t
JOIN n5 n ON SUBSTRING(','+Revenue, n.n, 1) = ','
AND n.n BETWEEN 1 AND Len(t.Revenue) ;
Careful, Jo. If you use that method for longer strings, it'll slow down worse than a WHILE loop because of the concatenation of the delimiters that you're using. I finally beat that problem when I rebuilt the "DelimitedSplit8K" function. Here's the link.
Hi Guys I tried most of your solutions but still not working. I would like to think the reason is because the value of Revenue R 12 000 R120 000 000
Has space between or I can say it is grouping the number based on size of the numbers. e.g R 12000 is stored as R12 000.
Gosh... try something on your own so that you understand it all because you're the one that will eventually have to support it.
The @jotorre_riversidedpss.org solution above will do everything you want if you tweak the "Revenue" column to REPLACE all spaces with empty strings and then CONVERT that semi-final answer to the DECIMAL datatype with your choice for precision and scale.
Thanks Jeff I'll look at that article.
Hi guys I have managed to fixed that issue. The issue there was the value R12 000 had no space. between 12 and the 3 zeros was not a space but some kind of a character that was used during development. so i copied the transparent character and put it in this query ' Replace(Replace(Revenue,'that character here',' '),'R',' ') and it works perfectly fine. Thanks for all your input
Credits to Navy Beans says that it's either a CHAR(160) (hard space) or maybe a CHAR(9) (TAB).
Anyway, very happy that you sussed it.