Convert String column to decimal

Transact-SQL
1

Hi

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.

2

IsNumeric function is not that accurate.
You should use a custom function or TRY_PARS , TRY_CONVERT

or
case when Replace(Replace(Revenue, ‘R’,’’),’ ‘,’’) NOT LIKE '%[^0-9]%' ...

3

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

1 Like
4

@Samrando,
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)
SELECT 
     t.ID
   , 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) ;

image

1 Like
5

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.
http://www.sqlservercentral.com/articles/Tally+Table/72993/

1 Like
6

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.

7

Gosh... try something on your own so that you understand it all because you're the one that will eventually have to support it. :wink:

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.

2 Likes
8

Thanks Jeff I'll look at that article.

9

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

10

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.