[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
Solved

# How can i sum varchar field.

Posted on 2002-07-24
Medium Priority
1,185 Views
I have a field in table OpenTrade varchar(21).
data as follows

---------
12332CR
12222DR
10000CR
10123CR
34133CR
10000CR

Now how can i add this if CR means Minus and DR minus +

0
Question by:bcp5190
[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
• 7
• 5
• 3
• +1

LVL 70

Expert Comment

ID: 7175507
Something like this may do it:

SELECT SUM( CAST(LEFT(openTrade,LEN(openTrade) - 2) AS INT) * CASE WHEN RIGHT(openTrade,2) = 'CR' THEN -1 ELSE 1 END )
FROM table1

0

LVL 70

Expert Comment

ID: 7175510
I would make it easier, though, by creating a computed column that contained the value and then using that in calculations.  For example:

ALTER TABLE table1
CASE WHEN RIGHT(openTrade,2) = 'CR' THEN -1 ELSE 1 END

Then:

FROM table1
0

Author Comment

ID: 7175617
In first method i'm geting following error

Syntax error converting the varchar value '         671,875.00' to a column of data type int.
0

Author Comment

ID: 7175643
In first method i'm geting following error

Syntax error converting the varchar value '         671,875.00' to a column of data type int.
0

LVL 70

Expert Comment

ID: 7175647
Oh, didn't realize it had commas in it.  Change to this:

SELECT SUM( CAST(REPLACE(LEFT(openTrade,LEN(openTrade) - 2),',','') AS DECIMAL(12,2)) * CASE WHEN RIGHT(openTrade,2) = 'CR' THEN -1 ELSE 1 END )
FROM table1

0

Author Comment

ID: 7175659
In first method i'm geting following error

Syntax error converting the varchar value '         671,875.00' to a column of data type int.
0

Author Comment

ID: 7175668
I'm getting following error now
Invalid length parameter passed to the substring function.
0

Author Comment

ID: 7175674
I'm getting following error now
Invalid length parameter passed to the substring function.
0

LVL 70

Expert Comment

ID: 7175687
Do you have some columns that are empty or don't have CR/DR?  Your initial posting of data made it look as if the data was consistent so I assumed that it was.  If it's not, you will have to add another CASE to test for an empty column and/or no CR/DR on the end.
0

Author Comment

ID: 7175712
I'm sorry but here is my actual data
---------
XXXXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXXXXXXXXXX
0.00
0.00
471,072,451.94CR
991,154,117.94DR
771,072,952.94CR
191,174,987.94DR
271,074,551.94CR
291,173,387.94DR

0

Author Comment

ID: 7175749
I'm sorry but here is my actual data
---------
XXXXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXXXXXXXXXX
0.00
0.00
471,072,451.94CR
991,154,117.94DR
771,072,952.94CR
191,174,987.94DR
271,074,551.94CR
291,173,387.94DR

0

LVL 70

Accepted Solution

Scott Pletcher earned 80 total points
ID: 7175774
Please try this expression for individual row value:

CASE WHEN RIGHT(openTrade,2) = 'CR'
THEN CAST(REPLACE(LEFT(openTrade,LEN(openTrade) - 2),',','') AS DECIMAL(13,2)) * -1
WHEN RIGHT(openTrade,2) = 'DR'
WHEN ISNUMERIC(openTrade) = 1
THEN CAST(REPLACE(openTrade,',','') AS DECIMAL(13,2))
ELSE 0

Or this for a total:
SUM(    CASE WHEN RIGHT(openTrade,2) = 'CR'
THEN CAST(REPLACE(LEFT(openTrade,LEN(openTrade) - 2),',','') AS DECIMAL(13,2)) * -1
WHEN RIGHT(openTrade,2) = 'DR'
WHEN ISNUMERIC(openTrade) = 1
THEN CAST(REPLACE(openTrade,',','') AS DECIMAL(13,2))
ELSE 0

I would strongly consider making this a computed column, like so:

CASE WHEN RIGHT(openTrade,2) = 'CR'
THEN CAST(REPLACE(LEFT(openTrade,LEN(openTrade) - 2),',','') AS DECIMAL(13,2)) * -1
WHEN RIGHT(openTrade,2) = 'DR'
WHEN ISNUMERIC(openTrade) = 1
THEN CAST(REPLACE(openTrade,',','') AS DECIMAL(13,2))
ELSE 0
END
0

LVL 1

Expert Comment

ID: 7176198
i have a comment... can u just have 3 fields instead? :D

Debit, Credit, Amount
0

LVL 1

Expert Comment

ID: 7176201
i have a comment... can u just have 3 fields instead? :D

Debit, Credit, Amount
0

LVL 1

Expert Comment

ID: 7176202
oops... remove the amount pls. :D just the credit and debit
0

LVL 9

Expert Comment

ID: 7176217
bcp,

are teh XXXX characters also part of data or it was just a way to beautify the presentation. It's kind of important. Now, if it is part of data how do you want it to be treated, as 0.00 ( ZERO ) or your appplication prefers it to be treated as NULL.

miron
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.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Viewers will learn how the fundamental information of how to create a table.
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.
###### Suggested Courses
Course of the Month13 days, 2 hours left to enroll

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

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