SQL Server：如何测试字符串是否只有数字字符
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28370295/
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
SQL Server : How to test if a string has only digit characters
提问by skyline01
I am working in SQL Server 2008. I am trying to test whether a string (varchar) has only digit characters (0-9). I know that the IS_NUMERIC function can give spurious results. (My data can possibly have $ signs, which should not pass the test.) So, I'm avoiding that function.
我在 SQL Server 2008 中工作。我试图测试字符串 (varchar) 是否只有数字字符 (0-9)。我知道 IS_NUMERIC 函数会给出虚假结果。（我的数据可能有 $ 符号，它不应该通过测试。）所以，我避免使用该功能。
I already have a test to see if a string has any non-digit characters, i.e.,
我已经有一个测试，看看一个字符串是否有任何非数字字符，即，
some_column LIKE '%[^0123456789]%'
I would think that the only-digits test would be something similar, but I'm drawing a blank. Any ideas?
我认为唯一数字测试会类似，但我正在画一个空白。有任何想法吗？
回答by P?????
Use Not Like
用 Not Like
where some_column NOT LIKE '%[^0-9]%'
Demo
演示
declare @str varchar(50)='50'--'asdarew345'
select 1 where @str NOT LIKE '%[^0-9]%'
回答by Bensonius
There is a system function called ISNUMERICfor SQL 2008 and up. An example:
对于 SQL 2008 及更高版本，有一个名为ISNUMERIC的系统函数。一个例子：
SELECT myCol
FROM mTable
WHERE ISNUMERIC(myCol)<> 1;
I did a couple of quick tests and also looked further into the docs:
我做了几个快速测试，并进一步查看了文档：
ISNUMERIC returns 1 when the input expression evaluates to a valid numeric data type; otherwise it returns 0.
ISNUMERIC returns 1 when the input expression evaluates to a valid numeric data type; otherwise it returns 0.
Which means it is fairly predictable for example
这意味着它是相当可预测的，例如
-9879210433would pass but 987921-0433does not.
$9879210433would pass but 9879210$433does not.
-9879210433会通过，但987921-0433不会。
$9879210433会通过，但9879210$433不会。
So using this information you can weed out based on the list of valid currency symbolsand +& -characters.
回答by Victor Barajas
Solution:
where some_column NOT LIKE '%[^0-9]%'Is correct.
解决方法：
where some_column NOT LIKE '%[^0-9]%'正确。
Just one important note: Add validation for when the string column = ''(empty string). This scenario will return that ''is a valid number as well.
只有一个重要的注意事项：当字符串 column = ''(空字符串)时添加验证。这种情况也将返回''一个有效数字。
回答by Dudi Konfino
DECLARE @x int=1
declare @exit bit=1
WHILE @x<=len('123c') AND @exit=1
BEGIN
IF ascii(SUBSTRING('123c',@x,1)) BETWEEN 48 AND 57
BEGIN
set @x=@x+1
END
ELSE
BEGIN
SET @exit=0
PRINT 'string is not all numeric -:('
END
END
