检查字符是否为数字的最快方法? [英] Fastest way to check if a character is a digit?

查看:111
本文介绍了检查字符是否为数字的最快方法?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我在使用sqlserver的ISNUMERIC函数时遇到问题,该函数为','返回true

I am having issues with sqlserver's ISNUMERIC function where it is returning true for ','

我正在解析邮政编码,并尝试查看第二个字符(假定为数字)是否为0,并且在每种情况下都做不同的事情.问题是我不能只通过首先检查isNumeric来转换字符.这是我的标量值函数的代码,该代码返回第二个char位置中的数字,如果不是数字,则返回-1.

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.

@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

因为当邮政编码不太有效时此操作失败.我只是想找出如何检查nvarchar @postal的第二个字符是否是0到9的数字.我已经看到了不同类型的解决方案,例如使用LIKE [0-9]或使用PATINDEX等.

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 PATINDEX etc.

是否有更好/更简便的方法来完成此操作?如果没有,哪种方法最快?

根据亚伦·伯特兰(Aaron Bertrand)的建议添加了代码

Code added as per Aaron Bertrand's suggestion

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)

推荐答案

如果您能够检测到WHERE col LIKE '[0-9]'与您提出的任何其他方法之间的任何区别,我将感到非常惊讶.但我同意Denis的观点,将其放在函数中,以便您在所有代码中始终使用相同的检查(或者至少,如果由于大扫描等原因而避免使用UDF,请在代码中放置一个标记,便于以后进行大规模更改.

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).

也就是说,最肯定的是,仅使用标量UDF会比使用函数内部解析的方法看到更多的性能下降.您确实应该比较UDF的性能与使用CASE进行内联的性能.例如

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.

SELECT Postal = CONVERT(INT, CASE WHEN SUBSTRING(postal,2,1) LIKE '[0-9]' 
       THEN SUBSTRING(postal, 2,1) END)
FROM ...

如果字符不是数字,这将产生NULL.

This will yield NULL if the character is not numeric.

如果仅处理检查局部变量,那么使用哪种解析方法就无关紧要了,最好将精力集中在其他地方.

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.

EDIT 向已演示的JOIN子句添加了建议.这可能会导致较少的恒定扫描,但可读性更高(子字符串调用等要少得多):

EDIT adding suggestion to demonstrated JOIN clause. This will potentially lead to less constant scans but is a lot more readable (far fewer substring calls etc):

;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);

这篇关于检查字符是否为数字的最快方法?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆