sql - SQL Server 中的转换函数

Question

我需要执行一个将 2 个字符转换为整数的函数。

规则是这样工作的：

99 = 99
9A = 100
9B = 101
9C = 102
9D = 103
.
.
.
9Z = 125
AA = 126
AB = 127

例如，我的函数收到“9C”并应返回“102”。该函数必须在 SQL 中完成。

这只是一个例子，存在的序列包含字母表的所有字符和从 0 到 9 的数字。

标签： sqlsql-serverfunction

Answer 1

这很有趣也很棘手。我使用了一个计数表来构建 ASCII 表并将 a dense_rankandrow_number应用于唯一字符和字符对。然后，结果是一个微不足道的case陈述。

declare @val varchar(2) = '9C'

;WITH
    E1(N) AS (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)),
    E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
    E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
    cteTally(N) AS 
    (
        SELECT  ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
    )

select
    RN = dense_rank() over (order by c.N)
    ,d = char(c.N)
    ,d2 = char(c.N) + char(c2.N)
    ,RN2 = row_number() over (order by c.N, c2.N) + 125
into #myTemp
from cteTally c
cross apply cteTally c2
where c.N between 65 and 90 and c2.N between 65 and 90

--uncomment this code to see how the ranking and row_number worked
--select * 
--from #myTemp
--order by RN

select
    case 
        when @val like '[0-9][0-9]' then @val
        when @val like '[9][A-Z]' then 99 + (select top 1 RN  from #myTemp where d = right(@val,1))
        when @val like '[A-Z][A-Z]' then (select RN2 from #myTemp where d2 = @val)
    end

drop table #myTemp