SQL查询混合整数和Varchar值的总和. [英] SQL Query For Sum of Mixed Integer and Varchar Values.

问题描述

我在一个表中有一个varchar(100)列，其中包含整数(作为字符串)和非整数字符串的混合.

例如


2个板块
3盘
10盘

我需要对这些整数值求和，结果变成这样.

"15个盘子"

是否有任何要显示的MS SQl查询.


请帮帮我.


谢谢

Dileep ....

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....

推荐答案

错误的设计.
您需要两列:
-金额列
-单位栏
然后你可以做一个

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

2个板
3盘
10盘

在上面所有字符串"plates"字符串中都是common.i希望以下sql查询对您有用

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.

这篇关于SQL查询混合整数和Varchar值的总和.的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！