Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL query
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 21:30pm
dilzz1.2K
Edited 16-Sep-12 21:39pm
v2
Comments
Andrei Straut at 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 at 17-Sep-12 3:20am
   
can u please gimme a example for that.
 
Thanks
 
Dileep
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

Wrong design.
You need two columns:
- a column for the amount
- a column for the unit
Then you can do a
SELECT SUM(amount) as total, unit
FROM MYTABLE
GROUP BY unit
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

2 Plates
3 Plates
10 Plates
 
in above all string "plates" string is common.i hope following sql query works for you
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.
  Permalink  
Comments
dilzz at 17-Sep-12 3:14am
   
Sir, Sum times string may b change..... some time it become.
"Glass"

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

  Print Answers RSS
0 OriginalGriff 245
1 Jochen Arndt 155
2 PIEBALDconsult 150
3 Afzaal Ahmad Zeeshan 120
4 DamithSL 115
0 OriginalGriff 5,695
1 DamithSL 4,591
2 Maciej Los 4,012
3 Kornfeld Eliyahu Peter 3,480
4 Sergey Alexandrovich Kryukov 3,190


Advertise | Privacy | Mobile
Web02 | 2.8.141220.1 | Last Updated 17 Sep 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100