blocks|key|29020|text|对于SQL2008和更高版本，有一个名为ISNUMERIC的系统函数。举个例子：|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|29021|SELECT+myCol
FROM+mTable
WHERE+ISNUMERIC(myCol)<>+1;|code-block|syntax|javascript|29022|我做了几个快速测试，还进一步查看了文档：|29023|ISNUMERIC+returns+1+when+the+input+expression+evaluates+to+a+valid+numeric+data+type;+otherwise+it+returns+0.|style|CODE|29024|例如，这意味着它是相当可预测的。|29025|-9879210433会通过，但987921-0433不会。$9879210433会通过，但9879210$433不会。|29026|因此，使用这些信息，您可以根据list+of+valid+currency+symbols和%2B+&+-字符进行剔除。|29027|entityMap|0|LINK|mutability|MUTABLE|url|https://msdn.microsoft.com/en-us/library/ms186272.aspx|1|https://msdn.microsoft.com/en-us/library/ms179882.aspx^0|K|9|0|0|0|0|0|31|0|0|0|B|G|B|U|B|1A|B|0|1A|1|1E|1|F|U|1|0^^$0|@$1|2|3|4|5|6|7|14|8|@]|9|@$A|15|B|16|1|17]]|C|$]]|$1|D|3|E|5|F|7|18|8|@]|9|@]|C|$G|H]]|$1|I|3|J|5|6|7|19|8|@]|9|@]|C|$]]|$1|K|3|L|5|6|7|1A|8|@$A|1B|B|1C|M|N]]|9|@]|C|$]]|$1|O|3|P|5|6|7|1D|8|@]|9|@]|C|$]]|$1|Q|3|R|5|6|7|1E|8|@$A|1F|B|1G|M|N]|$A|1H|B|1I|M|N]|$A|1J|B|1K|M|N]|$A|1L|B|1M|M|N]]|9|@]|C|$]]|$1|S|3|T|5|6|7|1N|8|@$A|1O|B|1P|M|N]|$A|1Q|B|1R|M|N]]|9|@$A|1S|B|1T|1|1U]]|C|$]]|$1|U|3|-4|5|6|7|1V|8|@]|9|@]|C|$]]]|V|$W|$5|X|Y|Z|C|$10|11]]|12|$5|X|Y|Z|C|$10|13]]]]

There is a system function called <a href="https://msdn.microsoft.com/en-us/library/ms186272.aspx" rel="noreferrer">ISNUMERIC</a> for SQL 2008 and up. An example:

<pre><code>SELECT myCol
FROM mTable
WHERE ISNUMERIC(myCol)&lt;&gt; 1;
</code></pre>

I did a couple of quick tests and also looked further into the docs:

<code>ISNUMERIC returns 1 when the input expression evaluates to a valid numeric data type; otherwise it returns 0.</code>

Which means it is fairly predictable for example

<code>-9879210433</code> would pass but <code>987921-0433</code> does not.
<code>$9879210433</code> would pass but <code>9879210$433</code> does not.

So using this information you can weed out based on the <a href="https://msdn.microsoft.com/en-us/library/ms179882.aspx" rel="noreferrer">list of valid currency symbols</a> and <code>+</code> &amp; <code>-</code> characters.

blocks|key|4536531|text|使用Not+Like|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|4536532|where+some_column+NOT+LIKE+'%25[%5E0-9]%25'|code-block|syntax|javascript|4536533|演示|4536534|declare+@str+varchar(50)='50'--'asdarew345'

select+1+where+@str+NOT+LIKE+'%25[%5E0-9]%25'|4536535|entityMap^0|2|8|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@$9|R|A|S|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|T|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|U|8|@]|D|@]|E|$]]|$1|M|3|N|5|H|7|V|8|@]|D|@]|E|$I|J]]|$1|O|3|-4|5|6|7|W|8|@]|D|@]|E|$]]]|P|$]]

Use <code>Not Like</code> 

<pre><code>where some_column NOT LIKE '%[^0-9]%'
</code></pre>

<h2>Demo</h2>

<pre><code>declare @str varchar(50)='50'--'asdarew345'

select 1 where @str NOT LIKE '%[^0-9]%'
</code></pre>

blocks|key|29041|text|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%2B1
END
ELSE
BEGIN
SET+@exit=0
PRINT+'string+is+not+all+numeric++-:('
END
END|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|29042|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>DECLARE @x int=1
declare @exit bit=1
WHILE @x&lt;=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
</code></pre>

blocks|key|29086|text|解决方案：where+some_column+NOT+LIKE+'%25[%5E0-9]%25'是正确的。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|29087|只有一个重要的注意事项:当字符串列=+''+(空字符串)时添加验证。这个场景将返回''也是一个有效的数字。|29088|entityMap^0|5|11|0|J|2|15|2|0^^$0|@$1|2|3|4|5|6|7|J|8|@$9|K|A|L|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|M|8|@$9|N|A|O|B|C]|$9|P|A|Q|B|C]]|D|@]|E|$]]|$1|H|3|-4|5|6|7|R|8|@]|D|@]|E|$]]]|I|$]]

Solution: 
 <code>where some_column NOT LIKE '%[^0-9]%'</code>
Is correct. 

Just one important note: Add validation for when the string column = <code>''</code> (empty string). This scenario will return that <code>''</code> is a valid number as well.

blocks|key|29138|text|我试图查找只包含数字、不包含标点符号或其他内容的字符串。我终于找到了一个可以工作的答案，here。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|29139|使用PATINDEX('%25%5E0-9%25'，some_column)+=0允许我过滤掉除实际数字字符串之外的所有内容。|29140|entityMap|0|LINK|mutability|MUTABLE|url|http://www.kuljeetsingh.com/How-To-Check-If-String-Contains-Numeric-Number-SQL-Server^0|18|4|0|0|0^^$0|@$1|2|3|4|5|6|7|N|8|@]|9|@$A|O|B|P|1|Q]]|C|$]]|$1|D|3|E|5|6|7|R|8|@]|9|@]|C|$]]|$1|F|3|-4|5|6|7|S|8|@]|9|@]|C|$]]]|G|$H|$5|I|J|K|C|$L|M]]]]

I was attempting to find strings with numbers ONLY, no punctuation or anything else. I finally found an answer that would work <a href="http://www.kuljeetsingh.com/How-To-Check-If-String-Contains-Numeric-Number-SQL-Server" rel="nofollow noreferrer">here</a>.
Using PATINDEX('%[^0-9]%', some_column) = 0 allowed me to filter out everything but actual number strings.

blocks|key|29161|text|所选答案不起作用。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|29162|declare+@str+varchar(50)='79D136'|code-block|syntax|javascript|29163|select+1+where+@str+NOT+LIKE+'%25[%5E0-9]%25'|29164|我没有解决方案，但我知道这个潜在的陷阱。如果你用字母'D‘代替科学记数法'E’，也是如此。|29165|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|D|7|O|8|@]|9|@]|A|$E|F]]|$1|I|3|J|5|6|7|P|8|@]|9|@]|A|$]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

The selected answer does not work.
<pre><code>declare @str varchar(50)='79D136'
</code></pre>
<pre><code>select 1 where @str NOT LIKE '%[^0-9]%'
</code></pre>
I don't have a solution but know of this potential pitfall. The same goes if you substitute the letter 'D' for 'E' which is scientific notation.

blocks|key|29179|text|将会工作的方法。上面使用它的方式将不起作用。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|29180|declare+@str+varchar(50)='79136'

select+
++case+
++++when++@str+LIKE+replicate('[0-9]',LEN(@str))+then+1+
++++else+0+
++end

declare+@str2+varchar(50)='79D136'

select+
++case+
++++when++@str2+LIKE+replicate('[0-9]',LEN(@str))+then+1+
++++else+0+
++end|code-block|syntax|javascript|29181|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Method that will work. The way it is used above will not work.
<pre><code>declare @str varchar(50)='79136'

select 
 case 
 when @str LIKE replicate('[0-9]',LEN(@str)) then 1 
 else 0 
 end

declare @str2 varchar(50)='79D136'

select 
 case 
 when @str2 LIKE replicate('[0-9]',LEN(@str)) then 1 
 else 0 
 end
</code></pre>

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.

I already have a test to see if a string has any non-digit characters, i.e.,

<pre><code>some_column LIKE '%[^0123456789]%'
</code></pre>

I would think that the only-digits test would be something similar, but I'm drawing a blank. Any ideas?

SQL Server : How to test if a string has only digit characters

SQL Server

我在SQL Server 2008中工作。我正在尝试测试一个字符串(varchar)是否只有数字字符(0-9)。我知道IS_NUMERIC函数可能会产生虚假的结果。(我的数据可能有$ signs，它不应该通过测试。)所以，我避免使用这个函数。我已经有一个测试来查看字符串是否有任何非数字字符，即，some_column ...

问SQL Server :如何测试字符串是否只有数字字符
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问SQL Server :如何测试字符串是否只有数字字符EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问SQL Server :如何测试字符串是否只有数字字符
EN