13,295,243 members (72,896 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
 Dave Kreskowiak 90 Richard Deeming 70 F-ES Sitecore 70 _duDE 65 OriginalGriff 65
 OriginalGriff 2,131 Jochen Arndt 1,180 Richard MacCutchan 1,090 ppolymorphe 917 Dave Kreskowiak 915