10-61 アルファベットを26進数に変換

SQLパズル

テーブル
Val
---
aa
ab
ac
az
ba

2文字のアルファベットの2文字目を、1文字進める

出力結果
変換前  変換後
------  ------
aa      ab
ab      ac
ac      ad
az      ba
ba      bb

SQL

col 変換前 for a8
col 変換後 for a8

--■■■26進数に変換する方法■■■
select Val as 変換前,
chr(trunc("26進数"/26)+ascii('a')) ||
chr(mod("26進数",26)  +ascii('a')) as 変換後
from (select Val,
      26*(ascii(substr(Val,1,1)) - ascii('a'))
      +   ascii(substr(Val,2,1)) - ascii('a') +1 as "26進数"
       from (select 'aa' as Val from dual
       union select 'ab' from dual
       union select 'ac' from dual
       union select 'az' from dual
       union select 'ba' from dual));

--■■■translate関数を使う方法■■■
select Val as 変換前,
case substr(Val,2)
when 'z'
then translate(substr(Val,1,1),'abcdefghijklmnopqrstuvwxyz','bcdefghijklmnopqrstuvwxyza')
else substr(Val,1,1) end ||
translate(substr(Val,2),'abcdefghijklmnopqrstuvwxyz','bcdefghijklmnopqrstuvwxyza') as 変換後
from (select 'aa' as Val from dual
union select 'ab' from dual
union select 'ac' from dual
union select 'az' from dual
union select 'ba' from dual);

解説

ascii関数を使うと、文字コードを取得できます

---