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
Medium Priority
413 Views
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
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
• 10
• 10

LVL 75

Expert Comment

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

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

ID: 16920996
I put the both values and on the above both are giving the structure ...
-34.8700
34.8700
0

Author Comment

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

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

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

0

Author Comment

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

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

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

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

Author Comment

ID: 16925222
Incorrect syntax near 'cast', expected 'as'.
0

LVL 75

Expert Comment

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

Author Comment

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

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

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

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

Author Comment

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

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

Author Comment

ID: 16926890

0

LVL 75

Expert Comment

ID: 16926909
ohh .. good :)
0

## Featured Post

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.
###### Suggested Courses
Course of the Month6 days, 9 hours left to enroll