oracle 仅使用 SQL 将 Base 36 转换为 Base 10
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2568668/
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
Base 36 to Base 10 conversion using SQL only
提问by EvilTeach
A situation has arisen where I need to perform a base 36 to base 10 conversion, in the context of a SQL statement. There doesn't appear to be anything built into Oracle 9, or Oracle 10 to address this sort of thing. My Google-Fu, and AskTom suggest creating a pl/sql function to deal with the task. That is not an option for me at this point. I am looking for suggestions on an approach to take that might help me solve this issue.
出现了一种情况，我需要在 SQL 语句的上下文中执行 base 36 到 base 10 的转换。Oracle 9 或 Oracle 10 中似乎没有内置任何内容来解决此类问题。我的 Google-Fu 和 AskTom 建议创建一个 pl/sql 函数来处理该任务。在这一点上，这对我来说不是一个选择。 我正在寻找有关可能帮助我解决此问题的方法的建议。
To put this into a visual form...
把它变成一个视觉形式......
WITH
Base36Values AS
(
SELECT '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ' myBase36 FROM DUAL
),
TestValues AS
(
SELECT '01Z' BASE36_VALUE,
71 BASE10_VALUE FROM DUAL
)
SELECT *
FROM Base36Values,
TestValues
I am looking for something to calculate the value 71, based on the input 01Z.EDIT - that is backwards... given 01Z translate it to 71.
我正在寻找一些东西来根据输入 01Z 计算值 71。编辑 - 这是倒退......给定 01Z 将其转换为 71。
As a bribe, each useful answer gets a free upvote.
作为贿赂，每个有用的答案都会得到一个免费的赞。
Thanks
谢谢
Evil.
邪恶。
回答by Dave Costa
select sum(position_value) from
(
select power(36,position-1) * case when digit between '0' and '9'
then to_number(digit)
else 10 + ascii(digit) - ascii('A')
end
as position_value
from (
select substr(input_string,length(input_string)+1-level,1) digit,
level position
from (select '01Z' input_string from dual)
connect by level <= length(input_string)
)
)
回答by Matthew Erwin
For T-SQL the following logic will perform the task that the Oracle code above does. This is generic general solution and will support Base-X to Base-10:
对于 T-SQL，以下逻辑将执行上述 Oracle 代码执行的任务。这是通用的通用解决方案，将支持 Base-X 到 Base-10：
select
sum(power(base,pos-1) *
case when substring(cnv,pos,1) between '0' and '9' then
cast(substring(cnv,pos,1) as int)
else 10 + ascii(upper(substring(cnv,pos,1))) - ascii('A') end)
from (values(reverse('01Z'), 36)) as t(cnv,base)
left join (values(1),(2),(3),(4),(5),(6)) as x(pos)
on pos <= len(cnv)
To use with other bases just use:
要与其他基础一起使用，只需使用：
from (select cnv = reverse('FF'), base=16) as t
or
或者
from (select cnv = reverse('101'), base=2) as t
Note that to support strings longer than 6 you would need to add more values to the position vector.
请注意，要支持长度超过 6 的字符串，您需要向位置向量添加更多值。
