Solved

converting varchar to money part 3

Posted on 2006-06-16
20
409 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

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

Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
sql query help 15 55
Can I skip a node in XML? 9 36
help converting varchar to date 14 25
Unable to connect to SQL Server 2005 database on Virtual Machine 8 20
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

730 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