3-18 文字列関数とignore nulls

SQLパズル

文字列テーブル
文字列
----------
1a
123
123b
12345
111111
1234567
12345678
1234567cd
1234555555

レコードごとに、
文字列のバイト数が、そのレコード以下で、
文字列に数字以外を含むレコードの文字列の、数字以外を出力する

出力結果
文字列      数字以外
----------  --------
1a          a
123         a
123b        b
12345       b
111111      b
1234567     b
12345678    b
1234567cd   cd
1234555555  cd

SQL

--■■■translate関数とignore nullsを使う方法(10g以降)■■■
select 文字列,
Last_Value(translate(文字列,'a0123456789','a') ignore nulls)
over(order by Lengthb(文字列)) as 数字以外
from (select '1a' as 文字列 from dual
union select '123'        from dual
union select '123b'       from dual
union select '12345'      from dual
union select '111111'     from dual
union select '1234567'    from dual
union select '12345678'   from dual
union select '1234567cd'  from dual
union select '1234555555' from dual);

--■■■RegExp_Replace関数とignore nullsを使う方法(10g以降)■■■
select 文字列,
Last_Value(RegExp_Replace(文字列,'[0-9]') ignore nulls)
over(order by Lengthb(文字列)) as 数字以外
from (select '1a' as 文字列 from dual
union select '123'        from dual
union select '123b'       from dual
union select '12345'      from dual
union select '111111'     from dual
union select '1234567'    from dual
union select '12345678'   from dual
union select '1234567cd'  from dual
union select '1234555555' from dual);

--■■■相関サブクエリを使う方法■■■
with WorkView as (select '1a' as 文字列 from dual
union select '123'        from dual
union select '123b'       from dual
union select '12345'      from dual
union select '111111'     from dual
union select '1234567'    from dual
union select '12345678'   from dual
union select '1234567cd'  from dual
union select '1234555555' from dual)
select 文字列,
(select translate(b.文字列,'a0123456789','a') from WorkView b
  where Lengthb(b.文字列) = (select max(Lengthb(c.文字列))
                               from WorkView c
                              where Lengthb(c.文字列) <= Lengthb(a.文字列)
                                and translate(c.文字列,'a0123456789','a') is not null)) as 数字以外
from WorkView a
order by Lengthb(文字列);

解説

Oracle10g以降なら、
文字列関数とignore nullsを組み合わせる方法があります

---