使用 SQL Server 从 char 列中仅选择整数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1611574/
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
Select only integers from char column using SQL Server
提问by Andy Select
How can I write a select statement to select only integers (and nothing more) from a char column in SQL Server. For example, my table name is POWDER with 2 columns, ID (int) and Name(char (5))
如何编写 select 语句以从 SQL Server 的 char 列中仅选择整数（仅此而已）。例如，我的表名是 POWDER，有 2 列，ID (int) 和 Name(char (5))
ID Name
-- ----------
1 AXF22
2 HYWWW
3 24680
4 8YUH8
5 96635
I want to be able to select only those rows that contain an integer and nothing more (ID 3 and ID 5 in this example)
我希望能够只选择那些包含整数的行，仅此而已（在此示例中为 ID 3 和 ID 5）
If I try:
如果我尝试：
SELECT *
FROM POWDER
WHERE Name LIKE '[0-9]%'
...it will return:
...它会返回：
ID Name
-- ----------
3 24680
4 8YUH8
5 96635
Any ideas how to get the rows containing just integers?
任何想法如何获取仅包含整数的行？
回答by Chris Latta
SELECT * FROM POWDER WHERE IsNumeric(Name) = 1
IsNumeric returns 1 for some other characters that are valid in numbers, such as + and - and $ but for your input you should be fine.
IsNumeric 为其他一些在数字中有效的字符返回 1，例如 + 和 - 和 $ 但对于您的输入，您应该没问题。
回答by Ilja
Try this:
尝试这个：
SELECT * FROM Table WHERE Name LIKE '[0-9]%%'
SELECT * FROM Table WHERE Name LIKE '[0-9]%%'
回答by gbn
To avoid issues with ISNUMERIC and all spaces, -, +, . etc, use the fact that the column is char(5)
为避免 ISNUMERIC 和所有空格出现问题，请使用 -、+、. 等，使用列是 char(5) 的事实
SELECT *
FROM POWDER
WHERE Name LIKE '[0-9][0-9][0-9][0-9][0-9]'
Edit: for any number of characters. Double negative...
编辑：对于任意数量的字符。双重否定...
SELECT *
FROM POWDER
WHERE Name NOT LIKE '%[^0-9]%'
回答by Anthony Faull
Use positive and negative checks to make sure we have an integer: It must contain a digit. Only digits and spaces are allowed. No spaces are allowed between digits.
使用正负检查来确保我们有一个整数：它必须包含一个数字。只允许数字和空格。数字之间不允许有空格。
SELECT *
FROM POWDER
WHERE Name LIKE '%[0-9]%'
AND Name NOT LIKE '%[^0-9 ]%'
AND Name NOT LIKE '%[0-9]% %[0-9]%'
回答by sathish
Try:
尝试：
SELECT *
FROM POWDER
WHERE Name patindex ('%[a-z]%',name) != 0
回答by Jay
The last one is the best,kind of works really really well.
最后一个是最好的，这种作品真的非常好。
SELECT * FROM POWDER
WHERE Name LIKE '%[0-9]%'
AND Name NOT LIKE '%[^0-9 ]%'
AND Name NOT LIKE '%[0-9]% %[0-9]%'
