Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

converting varchar to money part 3

Posted on 2006-06-16
20
Medium Priority
?
413 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

704 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