11,928,078 members (28,056 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.2K
Edited 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 214 Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ) 145 Richard Deeming 125 ppolymorphe 94 PIEBALDconsult 80
 OriginalGriff 7,740 KrunalRohit 4,723 Sergey Alexandrovich Kryukov 3,658 George Jonsson 2,970 Suvendu Shekhar Giri 2,331