【Oracle】改行コードを含むレコードを検索・取得する方法

Oracleにおける、改行コードを含むレコードを検索・取得する方法です。2パターンあります。

【スポンサーリンク】

改行コードを含むレコードを検索・取得
INSTR関数で改行コードを含むレコードを検索・取得
実行サンプル（INSTR関数）
LIKE演算子で改行コードを含むレコードを検索・取得
実行サンプル（LIKE演算子）
参考リンク
関連記事

改行コードを含むレコードを検索・取得

Oracleにおいて、改行コードを含むレコードを検索・取得する場合、以下2パターンがあります。

INSTR関数
LIKE演算子

どちらでも可能です。

この後、サンプルクエリを実行するためのテストデータです。

CREATE TABLE CRLF_TEST_TABLE (
    id NUMBER,
    TEST_COL VARCHAR2(100)
);
--CR
INSERT INTO CRLF_TEST_TABLE VALUES (1, '1行目' || CHR(13) || '2行目');
--LF
INSERT INTO CRLF_TEST_TABLE VALUES (2, '1行目' || CHR(10) || '2行目');
--CRLF
INSERT INTO CRLF_TEST_TABLE VALUES (3, '1行目' || CHR(13) || CHR(10) || '2行目');

テストデータの状態。

Oracle APEXだと改行されてない様に見えてしまうので、CSVをダウンロードして中身を確認。

改行されてますね。

これを用いて、INSTRとLIKEで実行します。

【スポンサーリンク】

INSTR関数で改行コードを含むレコードを検索・取得

INSTR関数を使用して改行コードを含むレコードを検索・取得する構文は以下。

--CR検索
SELECT *
  FROM テーブル名
 WHERE INSTR(カラム名, CHR(13)) > 0;

--LF検索
SELECT *
  FROM テーブル名
 WHERE INSTR(カラム名, CHR(10)) > 0;

--CRLF検索
SELECT *
  FROM テーブル名
 WHERE INSTR(カラム名, CHR(13) || CHR(10)) > 0;

INSTR関数をWHERE句に設定すると、改行コードを含むレコードを検索することが出来ます。

第1引数：対象のカラム
第2引数：改行コード

INSTR関数の第2引数に改行コードを入れるだけです。

【スポンサーリンク】

実行サンプル（INSTR関数）

INSTR関数を使用した場合の実行サンプルです。

--CR
SELECT 'CR' AS 改行コード
      ,id
      ,TEST_COL
  FROM CRLF_TEST_TABLE
 WHERE INSTR(TEST_COL, CHR(13)) > 0
UNION ALL
--LF
SELECT 'LF' AS 改行コード
      ,id
      ,TEST_COL
  FROM CRLF_TEST_TABLE
 WHERE INSTR(TEST_COL, CHR(10)) > 0
UNION ALL
--CRLF
SELECT 'CRLF' AS 改行コード
      ,id
      ,TEST_COL
  FROM CRLF_TEST_TABLE
 WHERE INSTR(TEST_COL, CHR(13) || CHR(10)) > 0;

結果はコチラ。

当たり前ですが、CR・LFを単独で条件に含めた場合でも、CRLFのレコードもヒットします。

例えば、「CLだけ」「LFだけ」を検索したい場合はWHERE句の条件を加えてあげる必要があります。「CRLF」のみはそのままで大丈夫です。

--CRだけ
SELECT 'CR' AS 改行コード
      ,id
      ,TEST_COL
  FROM CRLF_TEST_TABLE
 WHERE INSTR(TEST_COL, CHR(13)) > 0
   AND INSTR(TEST_COL, CHR(10)) = 0
   AND INSTR(TEST_COL, CHR(13) || CHR(10)) = 0
UNION ALL
--LFだけ
SELECT 'LF' AS 改行コード
      ,id
      ,TEST_COL
  FROM CRLF_TEST_TABLE
 WHERE INSTR(TEST_COL, CHR(10)) > 0
   AND INSTR(TEST_COL, CHR(13)) = 0
   AND INSTR(TEST_COL, CHR(13) || CHR(10)) = 0
UNION ALL
--CRLFだけ
SELECT 'CRLF' AS 改行コード
      ,id
      ,TEST_COL
  FROM CRLF_TEST_TABLE
 WHERE INSTR(TEST_COL, CHR(13) || CHR(10)) > 0;

これを再実行します。

これで「CLだけ」「LFだけ」を検索することが出来ました。

【スポンサーリンク】

LIKE演算子で改行コードを含むレコードを検索・取得

LIKE演算子を使用して改行コードを含むレコードを検索・取得する構文は以下。

--CR検索
SELECT *
  FROM テーブル名
 WHERE カラム名 LIKE '%' || CHR(13) || '%';

--LF検索
SELECT *
  FROM テーブル名
 WHERE カラム名 LIKE '%' || CHR(10) || '%';

--CRLF検索
SELECT *
  FROM テーブル名
 WHERE カラム名 LIKE '%' || CHR(13) || CHR(10) || '%';

LIKEの条件に改行コードを加えるだけです。

【スポンサーリンク】

実行サンプル（LIKE演算子）

LIKE演算子を使用した場合のサンプルです。

--CRだけ
SELECT 'CR' AS 改行コード
      ,id
      ,TEST_COL
  FROM CRLF_TEST_TABLE
 WHERE TEST_COL LIKE '%' || CHR(13) || '%'
   AND TEST_COL NOT LIKE '%' || CHR(10) || '%'
UNION ALL
--LFだけ
SELECT 'LF' AS 改行コード
      ,id
      ,TEST_COL
  FROM CRLF_TEST_TABLE
 WHERE TEST_COL LIKE '%' || CHR(10) || '%'
   AND TEST_COL NOT LIKE '%' || CHR(13) || '%'
UNION ALL
--CRLFだけ
SELECT 'CRLF' AS 改行コード
      ,id
      ,TEST_COL
  FROM CRLF_TEST_TABLE
 WHERE TEST_COL LIKE '%' || CHR(13) || CHR(10) || '%';

これを実行してみます。