如何在 SQL Server 2008 中将字符串转换为整数？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27049626/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to convert a string to integer in SQL Server 2008?
提问by Jaylen
I am being challenged by SQL Server. I have this simple query
我正受到 SQL Server 的挑战。我有这个简单的查询
SELET *
FROM mytable
WHERE ISNUMERIC(propertyvalue) = 1
AND CONVERT(int, CONVERT(decimal(9, 0), ISNULL(propertyvalue, 0 ))) > 0
I tried to change the conversion line from
我试图改变转换线
CONVERT(decimal(9, 0), ISNULL(propertyvalue, 0))
to
到
CONVERT(decimal(9, 2), ISNULL(propertyvalue, 0))
or
或者
CAST(ISNULL(propertyvalue, 0) AS numeric)
none of what I am trying is working what so ever. I keep getting this error
我正在尝试的一切都没有奏效。我不断收到此错误
Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.
消息 8114，级别 16，状态 5，第 1
行将数据类型 varchar 转换为数字时出错。
Any ideas on how to solve this problem?
关于如何解决这个问题的任何想法？
EDITED
已编辑
The propertyvalue has the type of varchar(255)and it has data like
属性值的类型varchar(255)和它的数据如下
2
1.5
2.1
String
1String 456
回答by Dave.Gugg
I believe you need to filter out the string data before trying the convert:
我相信您需要在尝试转换之前过滤掉字符串数据：
SELECT *
INTO #temp
FROM mytable
WHERE propertyvalue NOT LIKE '%[^0-9]%'
SELECT *
FROM #temp
WHERE CONVERT(INT, CONVERT(DECIMAL(9, 0), ISNULL(propertyvalue, 0))) > 0
DROP TABLE #temp
回答by lookslikeanevo
i think it was to do with the period, it doesnt know how to convert it.
我认为这与时期有关，它不知道如何转换它。
have you tried float?
你试过浮动吗？
CONVERT(float, ISNULL(propertyvalue, 0 ))
