トップページに戻る    次のSQLパズルへ    前のSQLパズルへ

10-120 指定文字以外の文字がないかチェック

SQLパズル

文字列の中に0123456789以外の文字がないかチェックする。

こちらを参考にさせていただきました


SQL

set timi on

--■■■変換する文字列が多い場合■■■
select Val
from (select RPad(LPad(to_char(RowNum),99999,'a'),99999,'b') as Val
      from all_objects)
where translate(Val,'a0123456789','a') is null;

select Val
from (select RPad(LPad(to_char(RowNum),99999,'a'),99999,'b') as Val
      from all_objects)
where LTrim(Val,'0123456789') is null;

select Val
from (select RPad(LPad(to_char(RowNum),99999,'a'),99999,'b') as Val
      from all_objects)
where RegExp_Replace(Val,'^[0-9]+') is null;

select Val
from (select RPad(LPad(to_char(RowNum),99999,'a'),99999,'b') as Val
      from all_objects)
where RegExp_Like(Val,'^[0-9]+$');

--■■■変換する文字列が少ない場合■■■
select Val
from (select RPad(LPad(to_char(RowNum),99999,'X'),99999,'Y') as Val
      from all_objects)
where translate(Val,'a0123456789','a') is null;

select Val
from (select RPad(LPad(to_char(RowNum),99999,'X'),99999,'Y') as Val
      from all_objects)
where LTrim(Val,'0123456789') is null;

select Val
from (select RPad(LPad(to_char(RowNum),99999,'X'),99999,'Y') as Val
      from all_objects)
where RegExp_Replace(Val,'^[0-9]+') is null;

select Val
from (select RPad(LPad(to_char(RowNum),99999,'X'),99999,'Y') as Val
      from all_objects)
where RegExp_Like(Val,'^[0-9]+$');


解説

LTrim関数を使えば、
translate関数を使うよりパフォーマンスが良くなります。

translate関数は、文字の変換を途中で打ち切らないですが
LTrim関数は、途中で打ち切る可能性があるからです。

LTrim関数の代わりにRTrim関数を使うと、
パフォーマンスは悪くなります。
文字列の格納方式が原因だと思われます。

■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
set timiでの速度計測の結果

SQL> select version from v$instance;

VERSION
-----------------
10.2.0.1.0

経過: 00:00:00.06
SQL>
SQL> set timi on
SQL>
SQL> --■■■変換する文字列が多い場合■■■
SQL> select Val
  2  from (select RPad(LPad(to_char(RowNum),99999,'a'),99999,'b') as Val
  3        from all_objects)
  4  where translate(Val,'a0123456789','a') is null;

レコードが選択されませんでした。

経過: 00:01:01.84

SQL> select Val
  2  from (select RPad(LPad(to_char(RowNum),99999,'a'),99999,'b') as Val
  3        from all_objects)
  4  where LTrim(Val,'0123456789') is null;

レコードが選択されませんでした。

経過: 00:00:35.25

SQL> select Val
  2  from (select RPad(LPad(to_char(RowNum),99999,'a'),99999,'b') as Val
  3        from all_objects)
  4  where RTrim(Val,'0123456789') is null;

レコードが選択されませんでした。

経過: 00:01:30.03

SQL> select Val
  2  from (select RPad(LPad(to_char(RowNum),99999,'a'),99999,'b') as Val
  3        from all_objects)
  4  where RegExp_Replace(Val,'^[0-9]+') is null;

レコードが選択されませんでした。

経過: 00:01:28.26

SQL> select Val
  2  from (select RPad(LPad(to_char(RowNum),99999,'a'),99999,'b') as Val
  3        from all_objects)
  4  where RegExp_Like(Val,'^[0-9]+$');

レコードが選択されませんでした。

経過: 00:01:26.92

SQL> --■■■変換する文字列が少ない場合■■■
SQL> select Val
  2  from (select RPad(LPad(to_char(RowNum),99999,'X'),99999,'Y') as Val
  3        from all_objects)
  4  where translate(Val,'a0123456789','a') is null;

レコードが選択されませんでした。

経過: 00:02:25.98

SQL> select Val
  2  from (select RPad(LPad(to_char(RowNum),99999,'X'),99999,'Y') as Val
  3        from all_objects)
  4  where LTrim(Val,'0123456789') is null;

レコードが選択されませんでした。

経過: 00:00:31.32

SQL> select Val
  2  from (select RPad(LPad(to_char(RowNum),99999,'X'),99999,'Y') as Val
  3        from all_objects)
  4  where RTrim(Val,'0123456789') is null;

レコードが選択されませんでした。

経過: 00:01:28.95

SQL> select Val
  2  from (select RPad(LPad(to_char(RowNum),99999,'X'),99999,'Y') as Val
  3        from all_objects)
  4  where RegExp_Replace(Val,'^[0-9]+') is null;

レコードが選択されませんでした。

経過: 00:01:28.35

SQL> select Val
  2  from (select RPad(LPad(to_char(RowNum),99999,'X'),99999,'Y') as Val
  3        from all_objects)
  4  where RegExp_Like(Val,'^[0-9]+$');

レコードが選択されませんでした。

経過: 00:01:25.62

5-25 全称肯定命題と存在否定命題