DBからCSV（カンマ区切り）やTSV（タブ区切り）でデータを出力するってことがたまにあるのですが、出力するデータに改行が入っているとCSVやTSVのフォーマットが崩れてしまってエクセルなどでうまく開けない時ってありますよね。
そんな時に幸せになれるかもしれない方法（PostgreSQL限定）を書いてみます。
素直に改行コードをreplaceで変換する
一つ目の方法は、まあ、単純に思いつきそうですが、replace関数で変換してしまう方法です。
変換対象の改行コードは以下のように表すことができます。
- chr(13) : CR（キャリッジリターン）
- chr(10) : LF（ラインフィード）
- chr(13)||chr(10) : CRLF
これをSQLで書いてみると、
select replace(replace(replace(description,chr(13)||chr(10),' '),chr(13),' '),chr(10),' ') from hogetable;
replaceをネストして上記3種類の改行コードを変換するんですね。
正規表現を使って改行コードを変換する
PostgreSQLには「regexp_replace」という正規表現で変換できる便利関数があるんですね！
ちなみに、MySQLには同じような関数はないはずです。どうだ！（なにが？）
正規表現なので、改行コードの書き方は下記のような感じ。
- \r : CR（キャリッジリターン）
- \n : LF（ラインフィード）
- \r\n : CRLF
同じくSQLで書いてみると、
select regexp_replace(description, '\(\r\n|\r|\n\)', ' ', 'g') from hogetable;
こうなります。
複数の条件は「|（パイプ）」でつなげますし、第3引数で「g」オプションを渡して、一致する全ての対象を変換しています。ルールをグループ化している「(」「)」をそれぞれ「\」バックスラッシュでエスケープしてるのが少し特徴的でしょうか。（その辺はドキュメント参照）
あと、TSVの場合タブも邪魔だったりするので、
select regexp_replace(description, '\(\r\n|\r|\n|\t\)', ' ', 'g') from hogetable;
こんな感じにすれば良さそうですね。
まとめ
まあ、色々やってみたんですが、CSVにしろTSVにしろデリミタと囲い文字がしっかりしてればフォーマットがくずれる事はないと思うんですけどね。覚えとくときっと便利ですので共有まで。
