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?
jay-areAsked:
Who is Participating?
 
Aneesh RetnakaranConnect With a Mentor Database AdministratorCommented:
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
 
Aneesh RetnakaranDatabase AdministratorCommented:
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
 
jay-areAuthor Commented:
The expected result for "-3487*0*0" would be  -$34.87.

Works fine except when I run across negative values.
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
Aneesh RetnakaranDatabase AdministratorCommented:
I put the both values and on the above both are giving the structure ...
-34.8700
34.8700
0
 
jay-areAuthor 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
 
jay-areAuthor 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
 
Aneesh RetnakaranDatabase AdministratorCommented:
So wht's the result you want for this column

0
 
jay-areAuthor 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
 
jay-areAuthor 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
 
Aneesh RetnakaranDatabase AdministratorCommented:
SELECT convert(money,dbo.RetSum(cast(replace(serviceinvoice_temp.nTXBL,'0*',','))as money) / 100,1)
0
 
jay-areAuthor Commented:
Incorrect syntax near 'cast', expected 'as'.
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
SELECT convert(money,CAST(dbo.RetSum((replace(serviceinvoice_temp.nTXBL,'*',',')))as money) / 100,1)
0
 
jay-areAuthor 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
 
Aneesh RetnakaranDatabase AdministratorCommented:
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
 
jay-areAuthor 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)
select serviceinvoice_temp.advisor,
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
 
Aneesh RetnakaranDatabase AdministratorCommented:
You dont need the create function statement there. Just the Insert statement is sufficient
0
 
jay-areAuthor Commented:
Thank you so much.  I've been working on this one for months now!  Plus you taught me something new.



0
 
Aneesh RetnakaranDatabase AdministratorCommented:
Its OK..What you are doing this week ends ? still at work ..!!
0
 
jay-areAuthor Commented:
When you run your own business everyday is a workday.  :)

0
 
Aneesh RetnakaranDatabase AdministratorCommented:
ohh .. good :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.