# converting varchar to money part 3

Hello experts!

This questions references my past questions here:  http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_21813695.html

The solution from this past question works great until I run across negative amounts in the varchar field.  Then I get the same cannot convert varchar to money error.

Here's the convert I use:

convert(money,cast(replace(replace(replace(serviceinvoice_temp.nTXBL,'0*',''),'*0',''),'*', '') as money) / 100,1)

So when data looks like this:  3487*0*0 it works fine.  When it looks like this:  -3487*0*0 it doesn't convert.

How do I fix this?
###### Who is Participating?

create function RetSum(
@CSVString       varchar(8000) )
returns int
as

begin
declare @tbl table (s int)
declare @i int ,
@j int
select       @i = 1
while @i <= len(@CSVString)
begin
select      @j = charindex(',', @CSVString, @i)
if @j = 0
begin
select      @j = len(@CSVString) + 1
end
insert      @tbl select substring(@CSVString, @i, @j - @i)
select      @i = @j + 1
end
return(SELECT  SUM(S)  FROM @Tbl)
end
GO
DECLARE @nTXBL varchar(2000)

SET @nTXBL = '102209*0*-1600'
select dbo.RetSum(replace (@nTxbl,'*',','))
0

What's the expected result

DECLARE @nTXBL varchar(2000)

SET @nTXBL = '-3487*0*0'

SELECT convert(money,cast(replace(replace(replace(@nTXBL,'0*',''),'*0',''),'*', '') as money) / 100,1)
0

Author Commented:
The expected result for "-3487*0*0" would be  -\$34.87.

Works fine except when I run across negative values.
0

I put the both values and on the above both are giving the structure ...
-34.8700
34.8700
0

Author Commented:
well using the convert above for this value:  0*0*-1600
I get an error "Cannot convert varchar value to money."

my temp table is all varchar
the final table I'm trying to insert/update to is money.

There are only 3 rows in this entire table that have negative amounts.  When I delete those rows and run my dts it works fine.  With the 3 in there I get the conversion error.
0

Author Commented:
Actually I just noticed that one row has this:  102209*0*-1600

So I'm assuming the convert works fine except when there are 2 values.  How do I fix that?
0

So wht's the result you want for this column

0

Author Commented:
Well I'm looking for a total dollar amount from this colum.  So 102209*0*-1600 should be converted to: \$1,006.09.

0

Author Commented:
create function RetSum(
@CSVString      varchar(8000) )
returns int
as

begin
declare @tbl table (s int)
declare @i int ,
@j int
select      @i = 1
while @i <= len(@CSVString)
begin
select     @j = charindex(',', @CSVString, @i)
if @j = 0
begin
select     @j = len(@CSVString) + 1
end
insert     @tbl select substring(@CSVString, @i, @j - @i)
select     @i = @j + 1
end
return(SELECT  SUM(S)  FROM @Tbl)
end
GO
DECLARE @nTXBL varchar(2000)

SET @nTXBL = serviceinvoice_temp.ntxbl
select dbo.RetSum(replace (@nTxbl,'*',','))

How do I set nTXBL to the actual db field?
0

SELECT convert(money,dbo.RetSum(cast(replace(serviceinvoice_temp.nTXBL,'0*',','))as money) / 100,1)
0

Author Commented:
Incorrect syntax near 'cast', expected 'as'.
0

SELECT convert(money,CAST(dbo.RetSum((replace(serviceinvoice_temp.nTXBL,'*',',')))as money) / 100,1)
0

Author Commented:
ok great!  Now if I have 3 different columns that need the same treatment?

I tried creating 2 more functions RetSum2 & 3 and changed the select statement to dbo.RetSum2 and dbo.RetSum3 but it doesn't recognize them.
0

you can reuse the same function..? whats the need to recreate other functions ? Also how you are calling the it ? can you post the exact statement
0

Author Commented:
Here is the insert statement with the function included:

create function RetSum(
@CSVString      varchar(8000) )
returns int as
begin
declare @tbl table (s int)
declare @i int ,
@j int
select      @i = 1
while @i <= len(@CSVString)
begin
select     @j = charindex(',', @CSVString, @i)
if @j = 0
begin
select     @j = len(@CSVString) + 1
end
insert     @tbl select substring(@CSVString, @i, @j - @i)
select     @i = @j + 1
end
return(SELECT  SUM(S)  FROM @Tbl)
end
GO

insert into serviceinvoice(advisor, ronum, mk, dept, svcopcodes, operationdesc, cwi, laboramt, billhr, jobcost, ntxbl, txbl, ptsjcost, yr, mileage, custnum, closeddate, jobtime, rodate, serialnum, fixedronum, ptssale, VOIDDATE)
serviceinvoice_temp.ronum,
serviceinvoice_temp.mk,
serviceinvoice_temp.dept,
serviceinvoice_temp.svcopcodes,
serviceinvoice_temp.operationdesc,
serviceinvoice_temp.cwi,
convert(money,serviceinvoice_temp.laboramt),
convert(money,serviceinvoice_temp.billhr),
convert(money,serviceinvoice_temp.jobcost),
convert(money,CAST(dbo.RetSum((replace(serviceinvoice_temp.nTXBL,'*',',')))as money) / 100,1),
convert(money,CAST(dbo.RetSum((replace(serviceinvoice_temp.TXBL,'*',',')))as money) / 100,1),
convert(money,CAST(dbo.RetSum((replace(serviceinvoice_temp.ptsjcost,'*',',')))as money) / 100,1),
serviceinvoice_temp.yr,
serviceinvoice_temp.mileage,
serviceinvoice_temp.custnum,
convert(datetime,serviceinvoice_temp.closeddate),
serviceinvoice_temp.jobtime,
convert(datetime, serviceinvoice_temp.rodate),
serviceinvoice_temp.serialnum,
serviceinvoice_temp.fixedronum,
convert(money,serviceinvoice_temp.ptssale),
convert(datetime,serviceinvoice_temp.voiddate)
from serviceinvoice_temp left outer join serviceinvoice on serviceinvoice.ronum = serviceinvoice_temp.ronum
where serviceinvoice.ronum is null and serviceinvoice.svcopcodes is null

When I try to execute this step it gives the error:  There is already an object named 'RetSum' in the database.

0

You dont need the create function statement there. Just the Insert statement is sufficient
0

Author Commented:
Thank you so much.  I've been working on this one for months now!  Plus you taught me something new.

0

Its OK..What you are doing this week ends ? still at work ..!!
0

Author Commented:

0