SQL：如何从 int 中获取左边的 3 个数字
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2640048/
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: how to get the left 3 numbers from an int
提问by dmr
I want to retrieve the left 3 numbers from an integer to be stored in a table. For example, if the int is 1234567, I want to retrieve 123. I want the second number (123) to also be an int; I don't want to convert anything to a string.
我想从要存储在表中的整数中检索左边的 3 个数字。例如，如果 int 是 1234567，我想检索 123。我希望第二个数字（123）也是一个 int；我不想将任何内容转换为字符串。
(And yes, really I should be working with strings. But I don't have control over that aspect of the issue.)
（是的，我真的应该使用字符串。但我无法控制问题的那个方面。）
Thank you!
谢谢！
回答by marc_s
For SQL Server, the easiestway would definitely be:
对于 SQL Server，最简单的方法肯定是：
SELECT CAST(LEFT(CAST(YourInt AS VARCHAR(100)), 3) AS INT)
Convert to string, take the left most three characters, and convert those back to an INT.
转换为字符串，取最左边的三个字符，然后将它们转换回 INT。
Doing it purely on the numerical value gets messy since you need to know how many digits you need to get rid of and so forth...
纯粹根据数值进行操作会变得混乱，因为您需要知道需要删除多少位等等......
If you want to use purely only INT's, you'd have to construct something like this (at least you could do this in SQL Server - I'm not familiar enough with Access to know if that'll work in the Access SQL "dialect"):
如果您只想仅使用 INT，则必须构建类似的内容（至少您可以在 SQL Server 中执行此操作 - 我对 Access 不够熟悉，不知道这是否适用于 Access SQL”方言”）：
DECLARE @MyInt INT = 1234567
SELECT
CASE
WHEN @MyInt < 1000 THEN @MyInt
WHEN @MyInt > 10000000 THEN @MyInt / 100000
WHEN @MyInt > 1000000 THEN @MyInt / 10000
WHEN @MyInt > 100000 THEN @MyInt / 1000
WHEN @MyInt > 10000 THEN @MyInt / 100
WHEN @MyInt > 1000 THEN @MyInt / 10
END AS 'NewInt'
But that's always an approximation - what if you have a really really really large number..... it might just fall through the cracks....
但这始终是一个近似值 - 如果您有一个非常非常大的数字怎么办.....它可能只是从裂缝中落下....
回答by JC Ford
Without casting to string, how about this?
不强制转换为字符串，怎么样？
(T-SQL)
(T-SQL)
select @i / power(10,floor(log10(@i))-2)
Throws an error if the int is less than 100, but seems to work otherwise.
如果 int 小于 100，则会引发错误，但似乎可以正常工作。
EDIT: To handle the error gracefully, you'd have to use a CASE since TSQL has no GREATEST() function...
编辑：要优雅地处理错误，您必须使用 CASE，因为 TSQL 没有 GREATEST() 函数...
select @i / case when @i < 100 then 1 else power(10,floor(log10(@i))-2) end
回答by Alex K.
In access SELECT clng(left(cstr(field), 3)) FROM Tshould work.
在访问SELECT clng(left(cstr(field), 3)) FROM T应该工作。
Edit: Infact I bet it wont care about the cstr().
编辑：事实上，我敢打赌它不会关心 cstr()。
回答by gbn
;WITH c10 AS
(
SELECT
Number
FROM
MyTable --?? WHERE Number>=1000
UNION ALL
SELECT Number/10 FROM c10 WHERE Number>=1000
)
SELECT Number FROM c10 WHERE Number < 1000
I can't test this, but it should do the trick. Iterate through until you end up with < 1000, relying on integer division. You may need to filter on the first clause to fine tune it
我无法测试这个，但它应该可以解决问题。迭代直到你得到 < 1000，依靠整数除法。您可能需要过滤第一个子句以对其进行微调
For a raw TSQL SQL Server 2005 solution only
仅适用于原始 TSQL SQL Server 2005 解决方案
回答by Doug Molineux
well if you have access to php you could use substr
好吧，如果您可以访问 php，则可以使用 substr
echo substr('1234567', 0, 3); and then convert the string back to an int
echo substr('1234567', 0, 3); 然后将字符串转换回 int
Converting an integer to a string in PHP
good luck!
祝你好运！
