如何只选择列中有数字的记录（SQL）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5251129/
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 select only records that have number in column (SQL)
提问by Rob
Basically I want to do this:
基本上我想这样做：
I want to return a record set converting one nvarchar value (ID) to an integer if it contains a number. If ID can be converted to a number, then add that row to the SELECTrecord set. If not, skip that row.
如果它包含一个数字，我想返回一个将一个 nvarchar 值 (ID) 转换为一个整数的记录集。如果 ID 可以转换为数字，则将该行添加到SELECT记录集中。如果没有，请跳过该行。
I think the SQL should look something like this.
我认为 SQL 应该是这样的。
(ID is nvarchar(10) in dbo.Table)
（ID 是 dbo.Table 中的 nvarchar(10)）
CREATE TABLE #Temp (ID int)
INSERT INTO #Temp SELECT ID FROM Table Where ISNumeric(Id)=0
But I get an error: nvarchar value 'Default' to data type int. If I do a SELECT CAST(ID as int)that does not work either.
但是我收到一个错误：nvarchar value 'Default' to data type int。如果我这样做SELECT CAST(ID as int)也不起作用。
回答by RichardTheKiwi
To be safe, forget about ISNUMERIC
为了安全起见，忘记 ISNUMERIC
If you are not expecting negative numbers, you can use this
如果您不期望负数，则可以使用此
INSERT INTO #Temp SELECT ID FROM Table
Where Id > '' -- is not the empty string and is not null
AND not ID like '%[^0-9]%' -- contains digits only
回答by SQLMenace
ISNumeric(Id)=0should be ISNumeric(Id)=1
ISNumeric(Id)=0应该 ISNumeric(Id)=1
However ISNUMERIC has a problem
但是 ISNUMERIC 有问题
run this
运行这个
SELECT ISNUMERIC('2d5'), ISNUMERIC('-'), ISNUMERIC('+')
Those all return 1
那些都返回 1
Take a look at IsNumeric, IsInt, IsNumberfor some ways to figure out if it can be converted to an integer
看看IsNumeric, IsInt, IsNumber的一些方法来确定它是否可以转换为整数
回答by Dragos
Maybe this one could help:
也许这个可以帮助：
select column_desired from requested_table <> 0 or is not null
