SQL文——18、正規表現関数

Oracle正規表現(regularexpression)
メタ文字
文字の意味
例を挙げて説明する
^
一致する文字列の開始位置([]で使用され、文字セットが受け入れられないことを示します.
^a:aで始まる文字列に一致
[^a]:aを含まない文字列を一致させる
-
a-m表現範囲で使用する場合.
最初の文字で使用する場合は
[-abc]などのハイフン列
 
$
一致文字の終了位置
「a$」:aで終わる文字列に一致
.
改行記号n以外の任意の単一文字を一致させる.
 
?
前のサブエクスプレッションを0回または1回一致させる
tr(y(ing)?):tryまたはtryingを表すことができます
*
前のサブエクスプレッションを0回または複数回一致させる
 
+
前のサブエクスプレッションを1回または複数回一致させる
 
()
サブエクスプレッションの開始位置と終了位置をマーク
a(b)*マッチ可能
ab,abb,abbb,youabb
(c|d)cまたはdに一致する
[]
中かっこ式のタグ付け
[cd]マッチング選択cまたはdは、
(c|d).一致するのは単一文字であり,[^cd]はc,d以外の単一文字である.
[a-z]はすべての小文字を表す
{m,n}
m=
 
|
またはの関係を表す.2つの項目の間の1つの選択を指定します.
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
キャラクタクラスタ
文字の意味
[[:alpha:]]
任意の文字
[[:digit:]]
任意の数値
[[:alnum:]]
任意の文字と数
[[:space:]]
空白文字
[[:upper:]]
任意の大文字
[[:lower:]]
任意の小文字
[[:punct:]]
任意の句読点
[[:xdigit:]]
任意の16進数は、[0-9 a-fA-F]に相当する.
 
 
 
 
 
 
 
 
regexp_like('S','RE','O')

S検索文字列または列式

RE正規コンテンツ

Oパラメータ(オプション)

idle> select ename from emp where regexp_like(ename,'S');

ENAME
----------
SMITH
JONES
SCOTT
ADAMS
JAMES

idle> select ename from emp where regexp_like(ename,'^S.*T');

ENAME
----------
SMITH
SCOTT

idle>

idle> idle> select ename from emp where regexp_like(ename,'^s.*T$','i');     

ENAME
----------
SCOTT

 
regexp_replace(S,'RE','NEW')

idle> select ename,regexp_replace(ename,'S..T','=') aaa from emp where regexp_like (ename,'S..T');

ENAME   AAA
---------- ----------
SMITH   =H
SCOTT   =T

複雑な使い方

SELECT REGEXP_REPLACE(
'Ellen Hildi Smith',
'(.*) (.*) (.*)', '\3, \1 \2')
FROM dual

REGEXP_REPLACE('EL
------------------
Smith, Ellen Hildi 

SQL文には、丸カッコで囲まれた3つの個別のサブエクスプレッションが表示されます.各サブエクスプレッションには、一致するメタ文字(.)が含まれます.改行以外の任意の文字が0回以上一致しなければならないことを示します.スペースは、各サブエクスプレッションを分割し、スペースも一致する必要があります.カッコは、値を取得するサブエクスプレッションを作成し、digitで参照できます.1番目のサブエクスプレッションは、1、2番目の2として割り当てられます.これらの後方参照は、この関数の最後のパラメータ(3,12)で使用され、この関数は置換サブ文字列を効果的に返し、カンマとスペースを含む所望のフォーマットで並べ替えられます.表11は、この正規表現の各構成要素を詳細に示す. 
REGEXP_COUNT
Oracleの11 gバージョンに導入され、この関数を使用して文字列の出現回数を統計します.
REGEXP_COUNT (source_char, pattern [, position [, match_param]])
source_char:正規処理が必要な文字列
pattern:一致する正規表現
position:開始位置、数文字目から正規表現マッチング(デフォルトは1)
match_param:モード(‘i’は大文字と小文字を区別しないマッチングに用いられる
「c」は大文字と小文字を区別するマッチングに使用される
‘n’許容句点(.)ワイルドカードとして改行文字を照合します.このパラメータを省略すると、文点は改行文字に一致しません.
「m」は、ソース列を複数行と見なす.つまり、Oracleでは、ソース・ストリング全体の開始または終了ではなく、^と$をソース・ストリング内の任意の場所の行の開始と終了と見なします.このパラメータを省略すると、Oracleはソース列を1行と見なします.
「x」はスペース文字を無視します.デフォルトでは、スペース文字は自己と一致します.
match_の場合paramは複数の矛盾する値を指定し、Oracleは最後の値を使用します.
 
                 )
REGEXP_SUBSTR
function REGEXP_SUBSTR(String, pattern, position, occurrence, modifier)

String:正規処理が必要な文字列

pattern:一致する正規表現

position:開始位置、数文字目から正規表現マッチング(デフォルトは1)

occurrence:いくつかのマッチンググループを識別し、デフォルトは1 modifier:モード('i'は大文字と小文字を区別せずに取得し、'c'は大文字と小文字を区別して取得し、デフォルトは'c')

例1:クエリが正規分割された最初の値、すなわち17を使用する

SELECT REGEXP_SUBSTR('17,20,23','[^,]+',1,1,'i') AS STR FROM DUAL;  

STR
-----
17

 
例2:クエリが正規分割された最後の値、すなわち23を使用する

SELECT REGEXP_SUBSTR('17,20,23','[^,]+',1,3,'i') AS STR FROM DUAL;  

STR
----
23

例3:文字列を行に分割

select regexp_substr('17,20,23','[^,]+',1,level)  AS STR
  from dual
connect by level<=regexp_count('17,20,23',',')+1

STR
----
17
20
23

例4:テーブル(複数行のデータ)がデータソースである場合、テーブルのフィールドを行に変換すると、デカルト積が表示されます.

SELECT lpad('~',(LEVEL-1)*5,'~')||CKJYM1 CKJYM1,LEVEL,regexp_substr(CKJYM1,'[^,]+',1,level) CKJYM
              FROM (SELECT 'B10,B04' CKJYM1
                      FROM dual
                      union all
                     SELECT 'B11,B12' CKJYM1
                      FROM dual ) T
            CONNECT BY LEVEL <= REGEXP_COUNT(CKJYM1,'B')  

 CKJYM1                      LEVEL         CKJYM
---------------------   -------------   ----------
B10,B04                       1              B10
~~~~~B10,B04                  2              B04
~~~~~B11,B12                  2              B12
B11,B12                       1              B11
~~~~~B10,B04                  2              B04
~~~~~B11,B12                  2              B12

CONNECT BYは再帰的な自己接続に相当し,各層の接続結果を結果セットに重ねていく.2階間の接続条件と再帰出口はCONNECT BYに記載されている.
ここではデータに親子関係はありませんが、同じ行のデータを繰り返すだけです.したがって,我々の接続条件はテーブルのプライマリキーCKJYM 1=PRIOR CKJYM 1にのみ適用され,またLEVEL制御層数を再帰出口として用いて所望のデータを得ることができる.しかし、ORACLEでは、前後の接続条件(CKJYM 1=PRIOR CKJYM 1)がある場合、同じ行のデータが再び表示されると、エラーが表示されます.ORA-01436:CONNECT BY loop in user data
それを騙すためにPRIOR DBMSを使ったRANDOM.VALUE(取得乱数に相当)は、DBMS_RANDOM.VALUEは、呼び出すたびに異なる結果を返すため、2行のデータが異なると判断し、エラーを報告しません.
だからsqlは次のように変更します.

SELECT lpad('~',(LEVEL-1)*5,'~')||CKJYM1 CKJYM1,LEVEL,regexp_substr(CKJYM1,'[^,]+',1,level) CKJYM
              FROM (SELECT 'B10,B04' CKJYM1
                      FROM dual
                      union all
                     SELECT 'B11,B12' CKJYM1
                      FROM dual ) T
            CONNECT BY LEVEL <=
                       REGEXP_COUNT(CKJYM1,'B')  
                       and CKJYM1=PRIOR CKJYM1
                       and prior dbms_random.value is not null

CKJYM1         LEVEL     CKJYM
------------  --------  ----------
B10,B04          1        B10
~~~~~B10,B04     2        B04
B11,B12          1        B11
~~~~~B11,B12     2        B12