Convert varchar(50) to money or decimal
-
Hi everyone:
I'm getting files with a column representing money as
+002345 which suppose to be $23.45
or
-002345 which suppose to be -$23.45
How to convert varchar(50) data to the right decimal or money format like $23.45 or -$23.45? I could accept 23.45 or -23.45 as decimals. Important part, I have to convert that during INSERT INTO table B.
RGDS
Sam
-
This was removed by the editor as SPAM
-
Well it's ugly but it works assuming that the right two digits are always supposed to represent cents.
declare @vchar varchar(10),
@Dollars varchar(10),
@Cents varchar(10),
@Amt varchar(11),
@Money money
set @vchar = '-0001234'
set @dollars = (left(@vchar, len(@vchar) -2))
set @cents = (right(@vchar, 2))
set @Amt = @dollars+'.'+@cents
set @money = cast(@amt as money)
print @dollars
print @cents
print @Amt
print Cast(@money as varchar(15))
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
-
Use the convert function. Here's an example with local declarations
declare
@inputval varchar(50), --your original string value
@converted decimal(10,2)
set @inputval = '-002345'
set @converted = convert(decimal(10,2),@inputval,0)
print @converted
-2345.00
-
That won't work because instead of -2345.00 it should be -23.45 at least according to what the OP said. Of course, having just said that I realize that as long as the original assumption holds that the rigtmost two digits will always represent hundreths the easiest method would be to use the convert but divide by 100, like so:
declare
@inputval varchar(50), --your original string value
@converted decimal(10,2)
set @inputval = '+002345'
set @converted = convert(decimal(10,2),@inputval,0)/100
print @converted
My original post is a classic example of how to make a difficult solution out of what should be a simple one
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply