oracle 如何使用oracle sql获取数字的前4位数字
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/36014465/
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
How to get first 4 digits in a number, with oracle sql
提问by NimChimpsky
I could do
我可以
select substr(to_char(20041111), 1, 4) FROM dual;
2004
Is there a way without converting to string first?
有没有办法不先转换为字符串？
回答by Tony Andrews
回答by vc 74
The following does not convert to a string but I'm not sure it's more readable...:
以下不会转换为字符串，但我不确定它是否更具可读性......：
select floor(20041111 / power(10, floor(log(10, 20041111) - 3)))
from dual;
log(10, 20041111) -> 8.3... meaning that 10 ^ 8.3... = 20041111
log(10, 20041111) -> 8.3... 意味着 10 ^ 8.3... = 20041111
if you floor this value, you get the number of digits in the base 10 representation of your number
如果您对这个值进行下限，您将获得以 10 为基数的数字表示的位数
if you want to remove digits, you just need to divide by 10 ^ (#digits - 3) (and not -4 since 10^1 already has 2 digits)
如果你想删除数字，你只需要除以 10 ^ (#digits - 3) （而不是 -4，因为 10^1 已经有 2 个数字）
回答by Matt
Another approach would be to use REGEXP_REPLACE
另一种方法是使用 REGEXP_REPLACE
SELECT REGEXP_REPLACE(20041111,'....$') FROM dual;
