SQL 检查字符是否为数字的最快方法？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7519283/
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
Fastest way to check if a character is a digit?
提问by Mike
I am having issues with sqlserver's ISNUMERIC function where it is returning true for ','
我在使用 sqlserver 的 ISNUMERIC 函数时遇到问题，它为“,”返回 true
I am parsing a postal code and trying to see if the second char (supposed to be a digit) is a 0 or not and do something different in each case. The issue is that I can't just cast the char by checking isNumeric first. Here is the code for my scalar-valued function to return the digit in the second char location, and -1 if it is not a digit.
我正在解析邮政编码并尝试查看第二个字符（应该是数字）是否为 0 并在每种情况下做不同的事情。问题是我不能通过首先检查 isNumeric 来投射字符。这是我的标量值函数的代码，用于返回第二个字符位置中的数字，如果它不是数字，则返回 -1。
@declare firstDigit int
IF ISNUMERIC(SUBSTRING(@postal,2,1) AS int) = 1
set @firstDigit = CAST(SUBSTRING(@postal,2,1) AS int)
ELSE
set @firstDigit = -1
RETURN @firstdigit
Since this fails when the postal code is not quite valid. I am just trying to find out how to check if the nvarchar @postal 's second character is a digit from 0-9. I have seen different types of solutions such as using LIKE [0-9]or using PATINDEXetc.
因为当邮政编码不是很有效时，这会失败。我只是想找出如何检查 nvarchar @postal 的第二个字符是否是 0-9 的数字。我见过不同类型的解决方案，例如使用LIKE [0-9]或使用PATINDEX等。
Is there a better/easier way to do this, and if not which method will be the fastest?
有没有更好/更简单的方法来做到这一点，如果没有，哪种方法最快？
EDIT: Code added as per Aaron Bertrand's suggestion
编辑：根据 Aaron Bertrand 的建议添加代码
ON z.postal =
CASE
WHEN CONVERT(INT, CASE WHEN SUBSTRING(v.patientPostal,2,1) LIKE '[0-9]'
THEN SUBSTRING(v.patientPostal, 2,1) END) = 0 then v.patientPostal
WHEN CONVERT(INT, CASE WHEN SUBSTRING(v.patientPostal,2,1) LIKE '[0-9]'
THEN SUBSTRING(v.patientPostal, 2,1) END) > 0 then LEFT(v.patientPostal,3)
回答by Aaron Bertrand
I'd be very surprised if you would ever be able to detect any difference between WHERE col LIKE '[0-9]'and any other methods you come up with. But I agree with Denis, put that away in a function so that you use the same check consistently throughout all your code (or at least, if you're avoiding UDFs because of large scans etc., put a marker in your code that will make it easy to change on a wide scale later).
如果您能够检测出WHERE col LIKE '[0-9]'与您提出的任何其他方法之间的任何差异，我会感到非常惊讶。但我同意丹尼斯，把它放在一个函数中，以便您在所有代码中始终如一地使用相同的检查（或者至少，如果您因为大扫描等而避免使用 UDF，请在您的代码中放置一个标记便于以后进行大规模更改）。
That said, you are most certainly going to see more of a performance hit just by using a scalar UDF than what method you use to parse inside the function. You really ought to compare performance of the UDF vs. doing that inline using CASE. e.g.
也就是说，与您在函数内部使用的解析方法相比，您肯定会看到仅通过使用标量 UDF 对性能的影响更大。您真的应该比较 UDF 的性能与使用CASE. 例如
SELECT Postal = CONVERT(INT, CASE WHEN SUBSTRING(postal,2,1) LIKE '[0-9]'
THEN SUBSTRING(postal, 2,1) END)
FROM ...
This will yield NULLif the character is not numeric.
NULL如果字符不是数字，这将产生。
If you are only dealing with checking local variables, it really is not going to matter what parsing method you use, and you are better off focusing your optimization efforts elsewhere.
如果您只处理检查局部变量，那么您使用的解析方法实际上并不重要，您最好将优化工作集中在其他地方。
EDITadding suggestion to demonstrated JOINclause. This will potentially lead to less constant scans but is a lot more readable (far fewer substring calls etc):
编辑向演示JOIN条款添加建议。这可能会导致更少的恒定扫描，但可读性更高（子字符串调用更少等）：
;WITH v AS
(
SELECT /* other columns, */ patientPostal,
ss = SUBSTRING(v.patientPostal,2,1),
FROM [whatever table is aliased v in current query]
)
SELECT /* column list */
FROM [whatever table is aliased z in current query]
INNER JOIN v ON z.postal = CONVERT(INT, CASE
WHEN v.ss = '0' THEN ss
WHEN v.ss LIKE '[1-9]' THEN LEFT(v.patientPostal, 3)
END);
回答by Wil
The best way to do it is this:
最好的方法是这样的：
IF SUBSTRING(@postal,2,1) LIKE [0-9]
CAST(SUBSTRING(@postal,2,1) AS int)
回答by SQLMenace
Take a look at IsNumeric, IsInt, IsNumberit has checks for those 3 types
看看IsNumeric, IsInt, IsNumber它检查了这 3 种类型
