Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

converting varchar to money part 3

Posted on 2006-06-16
20
Medium Priority
?
414 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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 

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 2000 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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

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