12,631,374 members (31,091 online)
Rate this:
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.

Thanks

Dileep....
Posted 16-Sep-12 21:30pm
dilzz1.3K
Updated 16-Sep-12 21:39pm
v2
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

Rate this:

## 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
Rate this:

## 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.
dilzz 17-Sep-12 3:14am

Sir, Sum times string may b change..... some time it become.
"Glass"

Top Experts
Last 24hrsThis month
 OriginalGriff 405 Peter Leow 195 Richard MacCutchan 96 F-ES Sitecore 90 F. Xaver 65
 OriginalGriff 1,834 ppolymorphe 1,192 Peter Leow 804 John Simmons / outlaw programmer 665 CPallini 561