仕事メモとか

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

カテゴリ: SQL


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/


postgresqlでoracleのminusみたいなものを探したら、あった(except)

はい、もうタイトルのままなんですが、
postgresqlでは呼び方が変わっていました。

正確にはoracleがminusで、
postgresqlとSQLserverがexceptという表記法でした。
使い方は両方一緒なので、とても簡単。

select aaa from table1
minus
select aaa from table2

これで、table1に居るなかで、table2に居る人を除外(差集合)となります。
なにかの調査の時に、割と切り出しを行うためにminusを使ったりするので(あんまり正式な調査としては良くないのですが)、
最初ないのかな? と思ったけど、あってよかった。


参考:
http://www.sql-reference.com/select/except_minus.html


微妙なカンマ区切りを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


DDL,DML,DCLについて纏めてみた

いつももやっと覚えてるんですが、人に説明するときにDDLだっけ?DMLだっけ?ってなるので改めてメモ。
(なんか昔メモした気もするんですが、探したけど見つからなかったので。かぶってたらすいません)

DDL=Data Definition Language
テーブルを作ったり削除したり変更したり権限。
create,drop,truncate table,alter tableなど

DML=Data Manipulation Language
テーブル操作関連。
select,insert,update,deleteなど

DCL=Data Control Language
トランザクション制御系。
begin,commit,rollbackなど

deleteはDML。
dropやtruncateはDDL。
この辺が落とし穴になりそうなので、要注意。



参考:
https://morizyun.github.io/database/sql-ddl-dml-dcl.html

↑このページのトップヘ