愿所有的美好和期待都能如约而至

仅使用 SQL 将 Base 36 转换为 Base 10

发布时间:  来源:互联网  作者:匿名  标签:base36 error Base 36 to Base 10 conversion using SQL only exception IT资讯 oracle   热度:37.5℃

本文介绍了仅使用 SQL 将 Base 36 转换为 Base 10的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

出现了一种情况,我需要在 SQL 语句的上下文中执行 base 36 到 base 10 的转换.Oracle 9 或 Oracle 10 中似乎没有内置任何内容来解决此类问题.我的 Google-Fu 和 AskTom 建议创建一个 pl/sql 函数来处理该任务.在这一点上,这对我来说不是一个选择.我正在寻找可能有助于我解决此问题的方法的建议.

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.

把它变成一个视觉形式…

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

我正在寻找根据输入 01Z 计算值 71 的东西.编辑 – 这是向后……给定 01Z 将其转换为 71.

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.

作为贿赂,每一个有用的答案都会得到一个免费的赞.

As a bribe, each useful answer gets a free upvote.

谢谢

邪恶.

推荐答案

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

这篇关于仅使用 SQL 将 Base 36 转换为 Base 10的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,

勇敢去编程!

勇敢的热爱编程,未来的你一定会大放异彩,未来的生活一定会因编程更好!

TOP