7-9 数値をカンマ区切りに編集

SQLパズル

数値をカンマ区切りに編集する。

SQL

--■■■to_char関数を使う方法■■■
select Val,to_char(Val,'99,9999,999') as CS
from (select 987654321 as Val from dual
union select  87654321 from dual
union select   7654321 from dual
union select    654321 from dual
union select     54321 from dual
union select      4321 from dual
union select       321 from dual
union select        21 from dual
union select         1 from dual)
order by Val desc;

--■■■正規表現を使う方法(10gでも動きません)■■■
--select Val,RegExp_Replace(Val,'(?<=[0-9])(?=(?:[0-9]{3})+$)',',') as CS
--from (select 987654321 as Val from dual
--union select  87654321 from dual
--union select   7654321 from dual
--union select    654321 from dual
--union select     54321 from dual
--union select      4321 from dual
--union select       321 from dual
--union select        21 from dual
--union select         1 from dual)
--order by Val desc;

解説

to_char関数でカンマ編集しています。

Oracle10gの正規表現では、
肯定先読み、肯定戻り読み、キャプチャしない括弧を使用できないようです。

---