SQL Query For Sum of Mixed Integer and Varchar Values.

Question

0.00/5 (No votes)

See more:

I have a varchar(100) column in a table that contains a mix of integers (as strings) and non-integer strings.

E.g.

2 Plates
3 Plates
10 Plates

I need to sum these integer values and the result become like this.

"15 Plates"

Is there any MS SQl query to show like that.

Please Help Me.

Thanks

Dileep....

Posted 16-Sep-12 20:30pm

dilzz

Updated 16-Sep-12 20:39pm

v2

Add a Solution

Comments

Andrei Straut 17-Sep-12 2:57am

You can try and take a look at regular expressions (even though MSSQL has very basic support for them, you can define your own). Using regexes, you could return only the numeric part of the VARCHAR column, and then use another query or subquery to do the the SUM of the returned numeric parts

dilzz 17-Sep-12 3:20am

can u please gimme a example for that.

Thanks

Dileep

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Bernhard Hiller · Answer 1 · 2012-09-16T22:12:00

Solution 2

Wrong design.
You need two columns:
- a column for the amount
- a column for the unit
Then you can do a

SQL

SELECT SUM(amount) as total, unit
FROM MYTABLE
GROUP BY unit

Posted 16-Sep-12 22:12pm

Bernhard Hiller

Vijay Walunj,Navi mumbai · Answer 2 · 2012-09-16T20:54:00

2 Plates
3 Plates
10 Plates

in above all string "plates" string is common.i hope following sql query works for you

SQL

select convert(varchar(max),convert(int,ltrim(rtrim(left('2 Plates',len('3 Plates')-len('Plates')))))+convert(int,ltrim(rtrim(left('3 Plates',len('3 Plates')-len('Plates')))))+convert(int,ltrim(rtrim(left('10 Plates',len('10 Plates')-len('Plates'))))))+right('10 Plates',len('Plates'))

if it will not help you inform i will give other soln.
make query dynamic as per your requirement.all the best.