SQL SQL中整数的长度（即十进制字符串的长度）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5377936/
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
Length of integer in SQL (i.e. length of decimal string)
提问by user390935
Quick version:Which is the best of the following and why? (or is there a better way):
快速版本：以下哪个是最好的，为什么？（或者，还有更好的方法）：
SELECT FLOOR(LOG10(Number))+1 AS NumLength FROM Table
SELECT LEN(CONVERT(VARCHAR, Number)) AS NumLength FROM Table
SELECT LEN(CAST(Number AS VARCHAR(10))) AS NumLength FROM Table
A bit more detail:
I wish to identify the most efficient mechanism for calculating the length of the string representation of an integer (more specifically a natural number - always >0).
更详细一点：
我希望确定计算整数（更具体地说是自然数 - 总是 >0）的字符串表示的长度的最有效机制。
I'm using MS SQL Server (2005).
我正在使用 MS SQL Server (2005)。
I've come up with the 3 solutions above, all of which seem to work fine.
我想出了上面的 3 个解决方案，所有这些似乎都可以正常工作。
I know the third version may have issues with very large integers, but for now we may assume that "Number" is never more than 9 decimal digits long.
我知道第三个版本可能会遇到非常大的整数问题，但现在我们可以假设“数字”的长度永远不会超过 9 位十进制数字。
Yet more detail:(you don't have to read this bit to answer my question)
This query is used heavily in a transaction processing environment.
Up to now, I have got away with the assumption that "Number" is always exactly 6 digits long.
However, now I must update the code to support anywhere from 4 to 9 digits.
还有更多细节：（你不必阅读这一点来回答我的问题）
这个查询在事务处理环境中被大量使用。
到目前为止，我已经摆脱了“数字”总是正好是 6 位数字的假设。
但是，现在我必须更新代码以支持 4 到 9 位数字。
This SQL is part of a condition to identify the card scheme of a card.
此 SQL 是识别卡的卡方案的条件的一部分。
The full query attempts to find the records matching the start of the card number against the Start and End range.
完整查询尝试根据开始和结束范围查找与卡号开头匹配的记录。
So full SQL condition would be something like this:
所以完整的 SQL 条件应该是这样的：
WHERE
-- Start and End match
((Start=End OR End=0) AND (Start=CAST(LEFT('<card number>', FLOOR(LOG10(Start))+1) AS BIGINT))) OR
-- Start != End
-- >= Start
(Start<=CAST(LEFT('<card number>', FLOOR(LOG10(Start))+1) AS BIGINT) AND
-- <= End
End>=CAST(LEFT('<card number>', FLOOR(LOG10(Start))+1) AS BIGINT))
NOTE:
I can redesign the table to use VARCHARs instead of INTs. This would allow me to use "LEN(Start)" instead of "FLOOR(LOG10(Start))+1)" however the condition will then have much more CASTs.
I'd prefer to continue to deal in INTs as the DB schema will stay the same, and in any case dealing with INTs should be faster than VARCHARs.
注意：
我可以重新设计表以使用 VARCHAR 而不是 INT。这将允许我使用“LEN(Start)”而不是“FLOOR(LOG10(Start))+1)”，但是条件将有更多的 CAST。
我更愿意继续处理 INT，因为 DB 模式将保持不变，并且在任何情况下处理 INT 都应该比 VARCHAR 更快。
IF I change the fields to VARCHARs, my condition might be:
如果我将字段更改为 VARCHAR，我的情况可能是：
WHERE
-- Start and End match
((Start=End OR LEN(End)=0) AND (Start=LEFT('<card number>', LEN(Start)))) OR
-- Start != End
-- >= Start
(CAST(Start AS BIGINT)<=CAST(LEFT('<card number>', LEN(Start)) AS BIGINT) AND
-- <= End
CAST(End AS BIGINT)>=CAST(LEFT('<card number>', LEN(Start)) AS BIGINT))
Many thanks for any help,
Dave
非常感谢您的帮助，
戴夫
采纳答案by Martin Smith
On my machine versions 2 and 3 come out about equal and beat the other two.
在我的机器上，版本 2 和 3 的结果大致相同并击败了其他两个。
Edit:Though it has just occurred to me that my original test was a bit unfair on CASEas ordering the statements in ascending numerical order means that only 10 possible numbers would meet the first condition and exit early. I've added an additional test below. You might also try nesting CASEstatements to do a binary search.
编辑：虽然我刚刚想到我的原始测试有点不公平，CASE因为按数字升序对语句进行排序意味着只有 10 个可能的数字会满足第一个条件并提前退出。我在下面添加了一个额外的测试。您也可以尝试使用嵌套CASE语句进行二分查找。
SET NOCOUNT ON
SET STATISTICS TIME ON
PRINT 'Test 1';
WITH E00(N) AS (SELECT 1 UNION ALL SELECT 1),
E02(N) AS (SELECT 1 FROM E00 a, E00 b),
E04(N) AS (SELECT 1 FROM E02 a, E02 b),
E08(N) AS (SELECT 1 FROM E04 a, E04 b),
E16(N) AS (SELECT 1 FROM E08 a, E08 b),
E32(N) AS (SELECT 1 FROM E16 a, E16 b),
cteTally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY N) FROM E32)
SELECT MAX(FLOOR(LOG10(N))+1)
FROM cteTally
WHERE N <= 10000000;
PRINT 'Test 2';
WITH E00(N) AS (SELECT 1 UNION ALL SELECT 1),
E02(N) AS (SELECT 1 FROM E00 a, E00 b),
E04(N) AS (SELECT 1 FROM E02 a, E02 b),
E08(N) AS (SELECT 1 FROM E04 a, E04 b),
E16(N) AS (SELECT 1 FROM E08 a, E08 b),
E32(N) AS (SELECT 1 FROM E16 a, E16 b),
cteTally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY N) FROM E32)
SELECT MAX(LEN(CONVERT(VARCHAR, N)))
FROM cteTally
WHERE N <= 10000000;
PRINT 'Test 3';
WITH E00(N) AS (SELECT 1 UNION ALL SELECT 1),
E02(N) AS (SELECT 1 FROM E00 a, E00 b),
E04(N) AS (SELECT 1 FROM E02 a, E02 b),
E08(N) AS (SELECT 1 FROM E04 a, E04 b),
E16(N) AS (SELECT 1 FROM E08 a, E08 b),
E32(N) AS (SELECT 1 FROM E16 a, E16 b),
cteTally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY N) FROM E32)
SELECT MAX(LEN(CAST(N AS VARCHAR(10))))
FROM cteTally
WHERE N <= 10000000;
PRINT 'Test 4';
WITH E00(N) AS (SELECT 1 UNION ALL SELECT 1),
E02(N) AS (SELECT 1 FROM E00 a, E00 b),
E04(N) AS (SELECT 1 FROM E02 a, E02 b),
E08(N) AS (SELECT 1 FROM E04 a, E04 b),
E16(N) AS (SELECT 1 FROM E08 a, E08 b),
E32(N) AS (SELECT 1 FROM E16 a, E16 b),
cteTally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY N) FROM E32)
SELECT MAX(CASE
WHEN N < 10 THEN 1
WHEN N < 100 THEN 2
WHEN N < 1000 THEN 3
WHEN N < 10000 THEN 4
WHEN N < 100000 THEN 5
WHEN N < 1000000 THEN 6
WHEN N < 10000000 THEN 7
WHEN N < 100000000 THEN 8
END)
FROM cteTally
WHERE N <= 10000000;
PRINT 'Test 5';
WITH E00(N) AS (SELECT 1 UNION ALL SELECT 1),
E02(N) AS (SELECT 1 FROM E00 a, E00 b),
E04(N) AS (SELECT 1 FROM E02 a, E02 b),
E08(N) AS (SELECT 1 FROM E04 a, E04 b),
E16(N) AS (SELECT 1 FROM E08 a, E08 b),
E32(N) AS (SELECT 1 FROM E16 a, E16 b),
cteTally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY N) FROM E32)
SELECT MAX(CASE
WHEN N >= 100000000 THEN NULL
WHEN N >= 10000000 THEN 8
WHEN N >= 1000000 THEN 7
WHEN N >= 100000 THEN 6
WHEN N >= 10000 THEN 5
WHEN N >= 1000 THEN 4
WHEN N >= 100 THEN 3
WHEN N >= 10 THEN 2
ELSE 1
END )
FROM cteTally
WHERE N <= 10000000;
Results from an example run on my machine are
在我的机器上运行的示例的结果是
Test 1
CPU time = 9422 ms, elapsed time = 9523 ms.
Test 2
CPU time = 7021 ms, elapsed time = 7130 ms.
Test 3
CPU time = 6864 ms, elapsed time = 7006 ms.
Test 4
CPU time = 9328 ms, elapsed time = 9456 ms.
Test 5
CPU time = 6989 ms, elapsed time = 7358 ms.
回答by Adam Robinson
To answer your question, the second version is clearer about what you actually want. Think about what someone looking at this code in six months will think: will they realize that the first version is trying obtain the length of a number represented in decimal, or will they think that you're performing some obscure mathematical operation that they can't find documentation requiring?
为了回答你的问题，第二个版本更清楚你真正想要什么。想想六个月后看到这段代码的人会怎么想：他们是否会意识到第一个版本正在尝试获取以十进制表示的数字的长度，或者他们是否会认为您正在执行一些他们可以做到的晦涩的数学运算” t 找到需要的文件？
More generally, though, you should probably consider storing these values as character data anyway, since they aren't representing real "numbers" to you (you aren't comparing based upon relative value, you aren't performing arithmetic, etc.). You can use CHECKconstraints to ensure that only numeric digits are in the field.
但是，更一般地说，您可能应该考虑将这些值存储为字符数据，因为它们并不代表真正的“数字”给您（您不是基于相对值进行比较，您不是在执行算术等） . 您可以使用CHECK约束来确保字段中只有数字。
I'm not clear on why storing them as character data would require conversions in your queries, assuming that you're consistent. There's also no reason to assume that dealing with ints would be faster than varchar, especially if there's conversion involved in both cases.
我不清楚为什么将它们存储为字符数据需要在您的查询中进行转换，假设您是一致的。也没有理由假设处理ints 会比 快varchar，尤其是在两种情况下都涉及转换的情况下。
