如何将varchar值转换为datatype integer [英] How to convert varchar value to datatype integer

问题描述

我的质量列是varchar类型，我想要获取该列的总和，但我在该字段中同时包含文本和数值。如果我运行以下查询我得到如下错误

将varchar值'100.00'转换为数据类型int时转换失败。

My quality column is varchar type and i want to take sum of that column but i have both text and numeric values in that field. if i run the below query i get error like below
"Conversion failed when converting the varchar value '100.00' to data type int."

select  distinct b.UserName,SUM (CONVERT(int,b.Quality)) as Amt  from table1 a, table2 b where b.UserName like b.USerName Group BY b.UserName ;

或者是否有其他方法可以在找到总和之前将质量转换为数字。



请帮我解决这个问题。

or is there any alternate way to convert the quality to numeric before finding the sum.

Please help me on this query.

推荐答案

你的桌子状况不好。

将数字与字符串和符号混合在一起单细胞。考虑到自由形式的价值观，试图将它们分开是痛苦的屁股。

这是错误组合的结果 - 错误的字段类型，标准化不佳以及没有正确的输入验证。

恕我直言，就像现在一样，在SQL级别解决它是不可行的，因为SQL不适用于此类事情。你可能想要在代码背后做到这一点但又是痛苦的屁股。最少的选择是重新设计此表。最后，我建议改造整个数据库，因为我怀疑其他表格也是坏形状。

了解更多信息：数据库设计简介 [ ^ ]

如果你不能这样做，你应该让专家为你做。数据库是应用程序的根目录，如果设计不正确，整个应用程序将是翻牌。

Your table is in bad shape.
Mixing numbers with strings and symbols in a single cell. It is "pain in the ass" to try to split them given the free form of values.
It is the result of a combination of mistakes - wrong field type, poor normalization, and without proper input validations.
IMHO, as it is now , it is not feasible to solve it at the SQL level as SQL is NOT meant for such things. You may want to do it at code behind but again it is 'pain in the ass'. The least option is to redesign this table. Ultimately, I would recommend revamping the whole database as I suspect the other tables are in bad shapes either.
Learn more about: Introduction to database design[^]
If you cannot do it, you should get an expert to do it for you. The database is the root of an application, if it is not designed correctly, the whole application will be a flop.

使用 CAST 功能

Use CAST function

select Cast(100.00 as int)

除了 Peter Leow的解决方案2 [ ^ ] ，我想提出解决方案 CASE WHEN ... END 。

In addition to solution 2 by Peter Leow[^], i'd like to propose solution with CASE WHEN... END.

SELECT SUM(NumberField)
FROM (
  SELECT CASE
        WHEN CHARINDEX('%25', PseudoNumericField) >) THEN CONVERT(INT, REPLACE(PseudoNumericField, '%25', ''))/100 
        WHEN ... THEN ... 
      END AS NumberField
) AS T

注意：％是特殊字符 [ ^ ]我们需要使用 25 逃脱它的特殊含义;）



见：

CASE WHEN ... END [ ^ ]

CHARINDEX [ ^ ]

REPLACE [ ^ ]

Note: % is a special character[^] and we need to use 25 to escape its special meaning ;)

See:
CASE WHEN ... END[^]
CHARINDEX[^]
REPLACE[^]

这篇关于如何将varchar值转换为datatype integer的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！