convert varchar to money ???? 1

[aspag]

Data was downloaded from a text file. Data type of Amtpay column currently is char(11). Amtpay have both negative and a positive numbers
Need to convert char to money

id amtpay
---------------- ------------
0415 .54
0416 1.67
0411 36.00
0405 1400.00
0412 11000.00
0404 - 7.50
0411 - 6500.00

Steps
a. alter column to varchar(12)
b. updated #temp set amtpay = left(amtpay,9) +'.'+right(amtpay,2)
c. Need to trim space for a negative and not successful

select *, paid=case when left(amtpay,1) = '-'
then left(amtpay,1) +substring((rtrim(ltrim(amtpay))),2,11)
else ltrim(amtpay) end
from #temp

First two steps may be redundant.
Any help will be greatly appreciated.

Thanks in advance.
spag

 

[TheJon]

As the data is fixed width, I will assume character 1 is reserved for a '-' sign only. The rest will be numeric.

try:

select convert(money, case when left(amtpay, 1) = '-' then '-' else '' end + ltrim(right(amtpay,11))) from #temp

[tt]|_ |_ _ | _ _
|, | )(/, |(_)| )
'[/tt]

 

[aspag]

You are great. It works.

Thanks a lot for you quick response Jon.

Can you please look at my old thread to which no one has replied when you get a chance?

It will be greatly appreciated.


spag