仕事メモとか

仕事中に調べた情報とか知ったことをメモしています。
unixコマンド, vim, oracle, putty, postgresql, bash, EXCEL, python, SQL全般 など。
最近は tableau, movabletype とかも触ったりしています。
雑な読書感想とかはこちら

カテゴリ: oracle


oracleの正規表現で少しハマった話_REGEXP_REPLACE

URLを含むjsonの一部のデータを抜き出したい、ってことよくありますよね。
そういうときに力を発揮するのが正規表現。

なんですが、どうも上手くいかなくて苦労した話です。
結果的に上手くいったんですが。

例)
データ:[{"url":"https://data.net/dat/?data_id=aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa","
...

後ろは延々と続きます。
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
のデータ部分が抜き出したいものです。
jsonに入ってるので、いろんなデータがありますが、このデータ自体は幸いjsonでも頭のほうに入っていました。

で、成功したSQLはこちら。

select regexp_replace(data_json, '.*sid=(.+?)\".*', '\1') as out_data
from data_table;

これで上手くいきました。


そして、失敗したのがこちら。

select regexp_replace(data_json, '.*sid=(.*?)\".*', '\1') as out_data
from data_table;

はい、取ってくる中を+から*に変更したものです。
原因はちゃんと見てないのですが、0件で最短マッチが成立してしまって上手く動かなかったのかな? と。
データ自体は必ず入っているのが確定しているデータなので今回はこれで困らないのですが、
もうちょっと原因が分かったほうがいい気が。



参考:
https://docs.oracle.com/cd/E16338_01/appdev.112/b56259/adfns_regexp.htm#CHDDGICJ
このエントリーをはてなブックマークに追加


oracle SQL Developer で文字をエスケープしようとしたときの話

はい、ちょっと環境が変わってSQL Developer使っています。
SQLで文字列を出すときに&(の半角)みたいなのって特殊文字扱いになるのですが、

select 'aaa & bbb' as dat from dual;

とすると、
「BBB:を入力せよ」みたいになります。
空で実行すると、こうなります。

aaa null

&BBBと解釈され、空がnullになるのでこうなったようです。

ちなみに、DBeaverで実行すると普通に実行されるので、
oracleとかSQLの仕様じゃなく、SQL Developerの仕様のようです。

それじゃエスケープすればいいのかな、と。思ってバックスラッシュとかいろいろやってみました。

select 'aaa \& bbb' as dat from dual;
select 'aaa && bbb' as dat from dual;
select '{aaa & bbb}' as dat from dual;

が、ダメでした。

調べた結果、以下の方法くらいしかなさそうでした。


1.&の後ろに文字指定をしない(後ろを別文字列として結合)
select 'aaa &' || ' bbb' as dat from dual;

2.設定変更
SET DEFINE OFF

3.chr(38)で出す
select 'aaa ' || chr(38) || ' bbb' as dat from dual;


ツールでちょっと見たい、が、作ったSQLを他でも流用したい、
みたいなときは、chr(38)にしておくと無難かと思います。



参考:
http://output-place.blogspot.com/2013/05/sqlplus.html
https://code-examples.net/ja/q/115aca
このエントリーをはてなブックマークに追加


SQLで正規表現でタブとか改行とか制御文字全般を削除する方法

chr(9) chr(10) chr(13)
とか呪いのように削除していましたが、
そもそも制御文字全般を消してしまえばよいのでは? ということで調べたら普通にありました。

[[:cntrl:]]

ということで、こいつを使うと簡単に行けました。

例:

select regexp_replace(dat, '[[:cntrl:]]', null)
from table

こんなに簡単になりました。



参考:
https://www.saka-en.com/oracle/sql-crlf-tab-delete-or-replace/
このエントリーをはてなブックマークに追加


微妙なカンマ区切りをSQLの正規表現で頑張る

はい、どちらかというと頭の悪い方向に進んでいる感が否めませんが、
取り急ぎ対応としては良く発生するものです。

こんなデータがあった時に、

,aa,,b,,,b,

これを複数カンマは1つに纏め、前後のカンマは無視する、という形に作り替えようと思います。

正規表現置換で対応してみました。
REGEXP_REPLACE

●実行例
select REGEXP_REPLACE(REGEXP_REPLACE(',aa,,b,,,b,', '[,]+',
','),'^,|,$','') as dat
from dual

最初に複数回発生しているものは全て1カンマに置換。
その後先頭、末尾のカンマはあれば消す、としています。


なんでこんなものを用意する必要があったのかというと、ListAggでデータを構築している場所があったのですが、
気が付くとデータが別列になっており、これらを合体させる必要があった。

{dat1},{dat2},{dat3} ...


とした場合、dat2とかのデータが空の場合、

{dat1},,{dat3}

となってしまい、ListAggの挙動と変わってしまうため、
こんなトリッキーな対応になりました。


参考
https://www.shift-the-oracle.com/sql/regular-expression-metacharacter.html
このエントリーをはてなブックマークに追加


DatabaseError: column at array pos 0 fetched with error: 1406 が出た

はい、なんか普段見ないエラーコード体系ですが、
この文字面で検索しても出なかったので、あえてこのタイトルそのままにしました。

結論から書くとORA-01406だと思うのですが、
これが出た原因はもう少し複雑でした。

エラーの内容は、適切なデータ型を使ってないため、
値が切り捨てられるという珍しいもの。

参考
https://www.oraexcel.com/oracle-11gR2-ORA-01406/lang-jp


該当SQLを調査してみると、

自動実行環境→エラーになる:ORA-01406
手動実行環境→エラーにならない

なので最初なにが起こっているかわからなかったのですが、
SQL内で特に何もしていなく割り算してる部分がありました。

A / B as xxx

これ、放っておくとBにゼロが入ってくる問題がありそうだったので、
ゼロ対応と、ついでに桁の明示指定をしたところ、上記のエラーも無くなりました。

to_char( case when B > 0 then A / B else 0 end, 'FM999.0000')

きっと割り算した結果、桁の問題から型が正しくなくなる、
みたいな状況だったのでしょう(憶測
このエントリーをはてなブックマークに追加

↑このページのトップヘ