Solved

converting varchar to money part 3

Posted on 2006-06-16
20
406 Views
Last Modified: 2010-05-18
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?
0
Comment
Question by:jay-are
  • 10
  • 10
20 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16920905
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 Comment

by:jay-are
ID: 16920949
The expected result for "-3487*0*0" would be  -$34.87.

Works fine except when I run across negative values.
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16920996
I put the both values and on the above both are giving the structure ...
-34.8700
34.8700
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

Author Comment

by:jay-are
ID: 16921054
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 Comment

by:jay-are
ID: 16921148
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
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16921258
So wht's the result you want for this column

0
 

Author Comment

by:jay-are
ID: 16921416
Well I'm looking for a total dollar amount from this colum.  So 102209*0*-1600 should be converted to: $1,006.09.  

0
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 500 total points
ID: 16921952
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
 

Author Comment

by:jay-are
ID: 16923213
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
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16925044
SELECT convert(money,dbo.RetSum(cast(replace(serviceinvoice_temp.nTXBL,'0*',','))as money) / 100,1)
0
 

Author Comment

by:jay-are
ID: 16925222
Incorrect syntax near 'cast', expected 'as'.
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16925226
SELECT convert(money,CAST(dbo.RetSum((replace(serviceinvoice_temp.nTXBL,'*',',')))as money) / 100,1)
0
 

Author Comment

by:jay-are
ID: 16925254
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
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16925293
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 Comment

by:jay-are
ID: 16926854
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
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16926861
You dont need the create function statement there. Just the Insert statement is sufficient
0
 

Author Comment

by:jay-are
ID: 16926879
Thank you so much.  I've been working on this one for months now!  Plus you taught me something new.



0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16926886
Its OK..What you are doing this week ends ? still at work ..!!
0
 

Author Comment

by:jay-are
ID: 16926890
When you run your own business everyday is a workday.  :)

0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16926909
ohh .. good :)
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question