SQL

SQL활용-3(계층형 쿼리, PIVOT, 정규표현식)

sue24 2022. 5. 20. 17:08

계층형 질의와 셀프 조인

테이블에 계층형 데이터가 있으면 계층형 질의로 조회해야 한다

계층형 데이터란 계층적으로 상위-하위가 나뉘는 데이터 ex) 관리자와 사원

순환 관계 데이터 모델이란? 셀프 조인이나 계층형 질의로 조회해야 하는 데이터로 사원인데 관리자도 되는 테이블을 순환 관계 데이터 모델이라고 한다

  1. 셀프 조인

    동일 테이블 사이에 조인을 하는 것

    식별하기 위해서 ALIAS를 반드시 사용하자

    FROM절에 동일 테이블이 두 번 이상 나온다

    • 매니저를 JONES로 두고 있는 부하 사원?

      SELECT B.EMPNO, B.ENAME, B.MGR
FROM EMP A, EMP B
WHERE A.ENAME = 'JONES'
    AND B.MGR = A.EMPNO;

      반대로 JONES의 관리자를 조회하려면

      두 번째 조건을 B.EMPNO = A.MGR로 바꾸면 된다

    • JONES의 부하의 부하 사원?

      SELECT C.EMPNO, C.ENAME, C.MGR
FROM EMP A, EMP B, EMP C
WHERE A.ENAME = 'JONES'
    AND B.MGR = A.EMPNO
    AND C.MGR = B.EMPNO;

    깊은 레벨의 노드를 조회하려면 셀프 조인이 반복되어야 한다

    그래서 오라클, SQL 서버는 순환관계 데이터 조회를 하기 위한 계층형 질의를 제공한다

  2. 계층형 질의

    DBMS에 따라 다르다

    1. 오라클

      • SELECT ... FROM ... WHERE ... AND ...
START WITH ... AND ...
CONNECT BY [NOCYCLE] ... AND FK = PRIOR PK
[ORDER SIBLINGS BY 컬럼명, 컬럼명,...];

        START WITH: 계층 구조 전개의 시작 위치. 액세스할 루트 데이터 지정. 생략 가능 1번만 수행한다

        CONNECT BY: 다음에 전개될 자식 데이터. 자식은 이 절의 조건 만족(조인). 결과가 없을 때까지 반복 수행한다

        PRIOR: connect by절(prior는 connect by, select, where에 사용 가능하다). 현재 읽을 칼럼을 지정한다
        (FK) = PRIOR (PK) : 부모 데이터 -> 자식 데이터로 순방향
        (PK) = PRIOR (FK) : 역방향

        NOCYCLE: 이미 나타났던 동일한 데이터가 또 나타나는 게 CYCLE로 런타임 오류가 나는데 이 옵션을 추가하면 오류가 나지 않는 대신 사이클이 발생하면 데이터 전개를 하지 않는다

        ORDER SIBLINGS BY: 형제 노드(동일 레벨) 사이에서 정렬

        루트1 - 자식, 루트2 - 자식처럼 루트가 2개 이상일 때,
        order siblings by에 의해 루트1 - 루트2 순이 되면
        루트1 - 자식(얘네도 지들 사이에서 order siblings by)한 다음에
        루트2 - 자식순으로!

        WHERE: 모든 전개 수행 후에 지정 조건을 만족하는 데이터만 추출한다(필터링)

        계층 쿼리는 WHERE절 다음에 기술하지만, 실제 실행 순서는 WHERE절보다 앞선다

      • 오라클은 계층형 질의시 가상 칼럼(슈도 칼럼) 제공

        LEVEL: 루트 데이터는 1. 그 하위 데이터는 2. 리프(leaf, 마지막) 데이터까지 1씩 증가

        계층 쿼리의 level은 전개방향 따라 다르다
        루트를 start with에서 정하기 나름..
        1 - 2 - 3 - 4 - 5에서 3이 역방향 전개 UNION 순방향전개를 하면
        3은 LV 1, 2, 4는 LV2, 1, 5는 LV3이 된다

        CONNECT_BY_ISLEAF: 전개 과정에서 리프 데이터는 1 아니면 0

        CONNECT_BY_ISCYCLE: 전개 과정에서 자식을 가진다 해당 데이터가 조상으로서 존재하면 1, 아니면 0. CYCLE 옵션을 사용했을 때만 가능하다

        SELECT 
    LEVEL, 
    LPAD(' ', (LEVEL-1)*2) || EMPNO AS EMPNO, MGR,
  CONNECT_BY_ISLEAF AS ISLEAF
FROM EMP
START WITH MGR IS NULL
CONNECT BY MGR = PRIOR EMPNO;
/*
7876에서 상위 관리자를 찾는 역방향 전개
START WITH EMPNO = 7876
CONNECT BY EMPNO = PRIOR MGR;
*/

        LPAD는 결과 들여쓰기를 위해 쓴 것

        LEVEL EMPNO MGR ISLEAF
        1 7839 N 0
        2 7566 7839 0
        3 7788 7566 0
        4 7876 7788 1
        3 7902 7566 0
        4 7369 7902 1

        7839 =>7566 => 7788 => 7876

        ​ => 7902 => 7369

        이렇게 1 => 2 => 3 => 4단계로 관리자에서 사원 순으로 들어간다

      • 오라클은 계층형 질의 중 편의를 위한 함수를 제공

        SYS_CONNECT_BY_PATH(칼럼, 경로 분리자): 루트 데이터부터 현재 전개할 데이터까지의 경로 표시

        CONNECT_BY_ROOT 칼럼: 현재 전개할 데이터의 루트 데이터를 표시한다.
        단항 연산자

        SELECT CONNECT_BY_ROOT(EMPNO) AS ROOT,
    SYS_CONNECT_BY_PATH(EMPNO, ', ') AS PATH,
    EMPNO, MGR
FROM EMP
START WITH MGR IS NULL
CONNECT BY MGR = PRIOR EMPNO;
        ROOT PATH EMPNO MGR
        7839 , 7839 7839 N
        7839 , 7839, 7566 7566 7836
        ...
        7839 ,7839, 7698 7698 7839
        7839 ,7839, 7698, 7499 7499 7698

    2. SQL서버

      2000 버전 이후부터 계층형 질의 문법을 지원한다

      CTE(common table expression)를 재귀 호출: 테이블 내 데이터의 최상위부터 하위로 계층 구조를 전개한다. 그러나 실제와 달라서 order by절을 추가해야 한다

      WITH EMPLOYEES_ANCHOR AS (
    SELECT EMPLOYEEID, LASTNAME, FIRSTNAME, 
              REPORTSTO, 0 AS LEVEL
  FROM EMPLOYEES
  WHERE REPORTSTO IS NULL /* 재귀 호출의 시작점 */

  UNION ALL

  SELECT R.EMPLOYEEID, R.LASTNAME, R.FIRSTNAME,
              R.REPORTSTO, A.LEVEL + 1
  FROM EMPLOYEES_ANCHORE A, EMPLOYEES R
  WHERE R.REPORTSTO = A.EMPLOYEEID
)
SELECT LEVEL, EMPLOYEEID, LASTNAME, FIRSTNAME, REPORTSTO
FROM EMPLOYEES_ANCHOR;
      LEVEL EMPLOYEEID LAST FIRST REPORT
      0 2 N
      1 1 2
      1 3 2
      1 4 2
      1 5 2
      1 8 2
      2 6 5

      WITH절에서 CTE 쿼리를 보면 UNION ALL로 두 쿼리가 결합된다

      둘 중 위의 쿼리를 앵커 멤버, 아래 쿼리를 재귀 멤버라고 한다

      재귀적 쿼리 실행 순서

      1. CTE식을 앵커 멤버와 재귀 멤버로 분할
      2. 앵커 멤버 실행 => 결과 집합(T0) 생성
      3. Ti는 입력으로 사용하고 Ti+1은 출력으로 사용해 지귀 멤버 실행
      4. 빈 집합을 반환할 때까지 3번을 반복
      5. 결과 집합 반환(이게 T0 ~ Tn까지의 UNION ALL)

      앵커 멤버가 시작점이자 outer 집합이 되어 inner 집합인 재귀 멤버와 조인한다 조인한 결과가 다시 outer 집합이 되어 재귀 멤버와 조인을 반복한다 조인 결과가 비어 더 이상 조인할 수 없으면 만들어진 결과 집합을 모두 합해서 리턴한다

      • 실제 조직도와 같은 결과를 출력하려면 CTE에 sort를 정렬용 칼럼으로 추가하여 쿼리 마지막에 order by조건을 추가한다(앵커 멤버와 재귀 멤버 양쪽에서 convert함수 등으로 데이터 형식을 일치시켜야 한다)

        WITH T_EMP_ANCHOR AS (
    SELECT EMPLOYEEID, MANAGERID, 0 AS LEVEL,
              CONVERT(VARCHAR(1000), EMPLOYEEID) AS SORT
    FROM T_EMP
  WHERE MANAGERID IS NULL

  UNION ALL

  SELECT R.EMPLOYEEID, R.MANAGERID, A.LEVEL + 1,
              CONVERT(VARCHAR(1000), A.SORT+','+R.EMPLOYEEID)
    FROM T_EMP_ANCHOR A, T_EMP R
  WHERE R.MANAGERID = A.EMPLOYEEID
)
SELECT 
    LEVEL, 
    REPLICATE(' ', LEVEL * 2) + EMPLOYEEID AS EMPLOYEEID,
    MANAGERID, SORT
FROM T_EMP_ANCHOR
ORDER BY SORT;
        LEVEL EMPLOYEEID MANAGERID SORT
        0 1000 N 1000
        1 1100 1000 1000,1100
        2 1110 1100 1000,1100,1110
        2 1120 1100 1000,1100,1120
        3 1121 1120 1000,1100,1120,1121
        3 1122 1120 1000,1100,1120,1122
        1 1200 1000 1000,1200
        2 1210 1200 1000,1200,1210

PIVOT절과 UNPIVOT절

PIVOT: 회전시키다

  1. PIVOT절

    행을 열로 회전시킨다(전환)

    PIVOT [XML](
    집계함수(표현식) [[AS] alias][, 집계함수...] /* 집계할 열 지정 */
    FOR (컬럼명[, 컬럼명...]) /* 피봇할 열 지정 */
    IN (표현식[, 표현식...] [[AS] alias] 
      | 서브쿼리
      | ANY[, ANY...]) /* 피봇할 열 값 지정 */
)

    • 피봇절은 집계함수와 for절에 지정되지 않은 열을 기준으로 집계된다

      인라인 뷰로 사용할 열을 지정해야 한다

      SELECT *
FROM (
    SELECT JOB, DEPTNO, SAL
  FROM EMP
)
PIVOT (
  SUM(SAL)
    FOR DEPTNO IN (10, 20, 30)
)
ORDER BY 1;
      JOB 10 20 30
      A N 6000 N
      C 1300 1900 950
      M 2450 2975 2850
      D 5000 N N
      S N N 5600

      SUM(SAL) AS SAL FOR DEPTNO IN (10 AS D10, 20 AS D20, 30 AS D30) 이렇게 하면 칼럼명이 D10_SAL(IN의 별칭_집계함수의 별칭), D20_SAL, D30_SAL로 바뀐다

      SUM(SAL) AS SAL FOR DEPTNO IN (10, 20, 30)으로만 바꾸면 10_SAL로..

      SELECT D20_SAL 이렇게 필요한 열만 조회하는 것도 가능하다

    • 다수의 집계함수가 지원된다

      SELECT *
FROM (
    SELECT JOB, DEPTNO, SAL
  FROM EMP
)
PIVOT (
  SUM(SAL) AS SAL, COUNT(*) AS CNT
    FOR DEPTNO IN (10 AS D10, 20 AS D20)
)
ORDER BY 1;
      JOB D10_SAL D10_CNT D20_SAL D20_CNT
      A N 0 6000 2
      C 1300 1 1900 2
      M 2450 1 2975 1
      D 5000 1 N 0
      S N 0 N 0

    • FOR절에 다수의 열 기술 가능(IN절에 다중 열 사용)

      SELECT *
FROM (
    SELECT TO_CHAR(HIREDATE, 'YYYY') AS YYYY, JOB, DEPTNO, SAL
  FROM EMP
)
PIVOT (
  SUM(SAL) AS SAL, COUNT(*) AS CNT
    FOR (DEPTNO, JOB) 
  IN ((10, 'A') AS D10A, (10, 'C') AS D10C, 
      (20, 'A') AS D20A, (20, 'C') AS D20C)
)
ORDER BY 1;
      YYYY D10A_SAL D10A_CNT D10C_SAL ... D20C_CNT
      1980 N 0 N 1
      1981 N 0 N 0
      1982 N 0 1300 0
      1987 N 0 N 1

    • 피봇절을 사용할 수 없으면 집계함수와 CASE 표현식으로!

      SELECT 
    JOB, 
    SUM(CASE DEPTNO WHEN 10 THEN SAL END) AS D10_SAL,
    SUM(CASE DEPTNO WHEN 20 THEN SAL END) AS D20_SAL,
    SUM(CASE DEPTNO WHEN 30 THEN SAL END) AS D30_SAL
FROM EMP
GROUP BY JOB
ORDER BY JOB;
      JOB D10_SAL D20_SAL D30_SAL
      A N 6000 N
      C 1300 1900 950
      M 2450 2975 2850
      D 5000 N N
      S N N 5600

  2. UNPIVOT절

    열이 행으로 전환된다

    UNPIVOT [{INCLUDE|EXCLUDE} NULLS](
    (컬럼명[, 컬럼명...]) /* 언피봇된 값이 들어갈 열 */
    FOR (컬럼명[, 컬럼명...]) /* 언피봇된 값을 설명할 값이 들어갈 열 */
    IN (표현식[, 표현식...]) /* 언피봇할 열과 설명할 값의 리터럴 값 */
)

    T1 테이블은 다음과 같다

    JOB D10_SAL D20_SAL D10_CNT D20_CNT
    A N 6000 0 2
    C 1300 1900 1 2 
    • SELECT JOB, DEPTNO, SAL
FROM T1
UNPIVOT INCLUDE NULLS (
    SAL
  FOR DEPTNO
  IN (D10_SAL AS 10, D20_SAL AS 20)
)
ORDER BY 1, 2;
      JOB DEPTNO SAL
      A 10 N
      A 20 600
      C 10 1300
      C 20 1900

      EXCLUDE NULLS면 첫번째 행이 안 나오게 된다

    • SELECT *
FROM T1
UNPIVOT (
    (SAL, CNT)
  FOR DEPTNO
  IN ((D10_SAL, D10_CNT) AS 10, (D20_SAL, D20_CNT) AS 20)
)
ORDER BY 1, 2;
      JOB DEPTNO SAL CNT
      A 10 N 0
      A 20 600 2
      C 10 1300 1
      C 20 1900 2 
    • SELECT *
FROM T1
UNPIVOT(
    (SAL, CNT)
  FOR (DEPTNO, DNAME)
  IN ((D10_SAL, D10_CNT) AS (10, 'Ac'), 
      (D20_SAL, D20_CNT) AS (20, 'Re')
)
ORDER BY 1, 2;
      JOB DEPTNO DNAME SAL CNT
      A 10 Ac N 0
      A 20 Re 6000 2
      C 10 Ac 1300 1
      C 20 Re 1900 2

    • 언피봇절을 사용할 수 없으면 카티션 곱을 사용한다.

      언피봇할 열의 개수만큼 행을 복제하고 CASE로 언피봇할 열을 선택한다

      SELECT A.JOB,
    CASE B.LV WHEN 1 THEN 10 WHEN 2 THEN 20 END AS DEPTNO,
    CASE B.LV WHEN 1 THEN A.D10_SAL 
                        WHEN 2 THEN A.D20_SAL END AS SAL,
    CASE B.LV WHEN 1 THEN A.D10_CNT 
                        WHEN 2 THEN A.D20_CNT END AS CNT
FROM T1 A,
         (SELECT LEVEL AS LV FROM DUAL CONNET BY LEVEL <= 2) B
ORDER BY 1, 2;
      JOB DEPTNO SAL CNT
      A 10 N 0
      A 20 600 2
      C 10 1300 1
      C 20 1900 2 
    • UNPIVOT(
    (SAL, CNT)
  FOR (DEPTNO, DNAME)
  IN ((D10_SAL, D10_CNT) AS (10, 'Ac'), 
      (D20_SAL, D20_CNT) AS (20, 'Re')...
  WHERE JOB = 'CLERK'
)
      JOB DEPTNO DNAME SAL CNT
      C 10 Ac 1300 1
      C 20 Da 1900 2
      C 30 Ch 950 1 
      UNPIVOT(
    (SAL, CNT)
  FOR DEPTNO
  IN ((D10_SAL, D10_CNT) AS 10, 
      (D20_SAL, D20_CNT) AS 20...
  WHERE JOB = 'CLERK'
)
      JOB DEPTNO SAL CNT
      C 10 1300 1
      C 20 1900 2
      C 30 950 1

      언피봇 컬럼 개수와 IN절의 컬럼 개수를 맞추고

      FOR절의 컬럼 개수와 IN절의 AS의 개수를 맞춘다

정규 표현식

문자열의 규칙을 표현하는 검색 패턴으로

문자열 검색 및 치환에 사용된다

  1. 기본 문법

    1. POSIX 연산자

      • . [dot]: 모든 문자와 일치한다(new line 제외)

      • | [or]: 대체 문자를 구분한다

      • \ [backslash]: \의 다음 문자를 일반 문자로 취급한다

        SELECT REGEXP_SUBSTR('aab', 'a.b'),
             REGEXP_SUBSTR('adc', 'a.b')
FROM DUAL;

        REGEXP_SUBSTR은 문자열에서 일치하는 패턴을 반환한다(없으면 NULL)

        상기 쿼리는 aab, NULL

        REGEXP_SUBSTR('a', 'a|b') => a

        REGEXP_SUBSTR('cd', 'ab|cd') => cd

        REGEXP_SUBSTR('a|b', 'a\|b') => a|b(|가 문자로 처리된 것)

        REGEXP_SUBSTR('a|b', 'a|b') => a

      • ^ [캐럿]: 문자열의 시작

      • $: 문자열의 끝

        REGEXP_SUBSTR('ab'||'CHR(10)'||'cd', '^.', 1, 1) => a
        chr(10)은 개행 문자 반환
        전체 문자열의 첫번째 시작문자

        REGEXP_SUBSTR('ab'||'CHR(10)'||'cd', '^.', 1, 2) => null
        전체 문자열의 두번째 시작문자

        REGEXP_SUBSTR('ab'||'CHR(10)'||'cd', '.$', 1, 1) => d

        REGEXP_SUBSTR('ab'||'CHR(10)'||'cd', '.$', 1, 2) => null

      • 수량사

        선행 표현식의 일치 횟수를 지정한다

        패턴을 최대로 일치시키는 GREEDY 방식

        • ? : 0회/1회
        • * : 0회 이상
        • + : 1회 이상
        • {m} : m회 일치
        • {m, } : 최소 m회 일치
        • {, m} : 최대 m회 일치
        • {m, n}: 최소 m회 최대 n회 일치

        REGEXP_SUBSTR('ac', 'ab?c') => ac (ac, abc 가능)

        REGEXP_SUBSTR('abc', 'ab?c') => abc

        REGEXP_SUBSTR('abbc', 'ab?c') => null

        REGEXP_SUBSTR('abc', 'ab?c') => abc

        REGEXP_SUBSTR('ac', 'ab*c') => ac

        REGEXP_SUBSTR('abc', 'ab*c') => abc

        REGEXP_SUBSTR('abbc', 'ab*c') => abbc(ac, abc, abbc, abbbc)

        REGEXP_SUBSTR('ac', 'ab+c') => null

        REGEXP_SUBSTR('abc', 'ab+c') => abc

        REGEXP_SUBSTR('abbc', 'ab+c') => abbc(abc, abbc, abbbc, abbbbc..)

        REGEXP_SUBSTR('ab', 'a{2}') => null

        REGEXP_SUBSTR('aab', 'a{2}') => aa

        REGEXP_SUBSTR('aab', 'a{3,}') => null

        REGEXP_SUBSTR('aaab', 'a{3,}') => aaa (aaa, aaaa...)

        REGEXP_SUBSTR('aaab', 'a{4, 5}') => null

        REGEXP_SUBSTR('aaaab', 'a{4, 5}') => aaaa(aaaa, aaaaa)

      • (expr) : 검색 패턴의 시작과 끝 지정. 괄호 안을 하나의 단위로 취급한다

        REGEXP_SUBSTR('ababc', '(ab)+c') => ababc(abc, ababc..)

        REGEXP_SUBSTR('ababc', 'ab+c') => abc(abc, abbc..)

        REGEXP_SUBSTR('abd, 'a(b|c)d') => abc(abc, acd)

        REGEXP_SUBSTR('abd', 'ab|cd') => ab(ab, cd)

      • \n : n번째 서브 표현식과 일치하는 식

        n은 1~9 사이의 정수

        역 참조. 일치한 서브 표현식을 재참조 가능하게 한다.

        반복 패턴 검색 및 서브 표현식 위치 변경에 활용

        REGEXP_SUBSTR('abxbc', '(ab|cd)x\1') => abxab(abxab, cdxcd)

        REGEXP_SUBSTR('abxef', '(ab|cd)x\1') => null

        REGEXP_SUBSTR('ababab', '(.*)\1+') => ababab

        REGEXP_SUBSTR('abcabc', '(.*)\1+') => abcabc

        REGEXP_SUBSTR('abcabd', '(.*)\1+') => null

      • 문자리스트

        문자를 대괄호로 묶은 표현식

        리스트 중 한 문자만 일치하면 일치하는 것

        [] 내부의 하이픈(-)은 범위 연산자로 작동한다

        • [char] : 문자 리스트 중 한 문자와 일치

        • [^char] : 문자 리스트에 포함되지 않은 한 문자와 일치

          [ab]c => ac, bc

          [^ab]c => ac, bc가 아닌 문자열

          [0-9][a-z] => 숫자 + 영소문자로 두 자리

          [^0-9][^a-z] => 문자 + (영소문자 아닌 것)으로 두 자리

        • [:digit:] = [0-9]

        • [:lower:] = [a-z]

        • [:upper:] = [A-Z]

        • [:alpha:] = [a-zA-Z] 영문자

        • [:alnum:] = [0-9a-zA-Z] 영문자와 숫자

        • [:xdigit:] = [0-9a-fA-F] 16진수

        • [:punct:] = [^[:alnum:][:cntrl:]] 구두점 기호

        • [:blank:] : 공백 문자

        • [:space:] : 공간 문자(스페이스, 엔터, 탭)

    2. PERL 정규 표현식 연산자

      • \d : 숫자 [[:digit:]]

      • \D : 숫자 외 모든 문자 [^[:digit:]]

      • \w : 숫자와 영문자(언더바 포함) [[:alnum:]_]

      • \W : 숫자와 영문자 외 모든 문자(언더바 제외) [^[:alnum:]_]

      • \s : 공백 문자 [[:space:]]

      • \S : 공백 문자 외 모든 문자 [^[:space:]]

        REGEXP_SUBSTR('(650)555-0100', '^\(\d{3})\d{3}-\d{4}$') => (650)555-0100 (괄호 없으면 null)

        REGEXP_SUBSTR('b2b', '\w\d\D') => b2b

        REGEXP_SUBSTR('b22', '\w\d\D) => null

        REGEXP_SUBSTR('jdoe@company.co.uk', '\w+@\w+(\.\w+)+') => 'jdoe@company.co.uk' (.\w+)+는 .숫자문자가 1회 이상 반복된다는 뜻

        REGEXP_SUBSTR('to:bill', '\w+\W\s\w+') => to:bill

        REGEXP_SUBSTR('(a b )', '\(\w\s\w\s\)') => (a b )

        REGEXP_SUBSTR('(a,b.)', '\(\w\S\w\S\)') => (a,b.)

      • perl은 UNGREEDY 방식!(최소한만!)

        • ?? : 0회/1회 일치

        • *? : 0회 이상

        • +? : 1회 이상

        • {m}? : m회

        • {m,}? : 최소 m회

        • {, m}? : 최대 m회

        • {m, n}? : 최소 m회 최대 n회

          REGEXP_SUBSTR('aaaa', 'a??aa') => aa(nongreedy 패턴 최소로 일치)

          REGEXP_SUBSTR('aaaa', 'a?aa') => aaa(greedy 패턴 최대로 일치)

          REGEXP_SUBSTR('xaxbxc', '\w*?x\w') => xa

          REGEXP_SUBSTR('xaxbxc', '\w*x\w') => xaxbxc

          REGEXP_SUBSTR('abxcxd', '\w+?x\w') => abxc

          REGEXP_SUBSTR('abxcxd', '\w+x\w') => abxcxd

          REGEXP_SUBSTR('aaaa', 'a{2}?') => aa

          REGEXP_SUBSTR('aaaa', 'a{2}') => aa

          REGEXP_SUBSTR('aaaaa ', 'a{2, }?') => aa

          REGEXP_SUBSTR('aaaaa ', 'a{2,}') => aaaaa

          REGEXP_SUBSTR('aaaaa ', 'a{2, 4}?') => aa

          REGEXP_SUBSTR('aaaaa ', 'a{2, 4}') => aaaa

    • WHERE C1 LIKE '1_%%' ESCAPE '\';

      escape 문자를 \로 지정한 것

      x1...%y: x는 아무 것도 없어야 하고 y는 있어도 되고 없어도 된다

  2. 정규 표현식 조건과 함수

    1. REGEXP_LIKE(source_char, pattern[, match_param])

      source_char와 pattern이 일치하면 true, 아니면 false 반환

      source_char: 검색할 문자열

      pattern: 검색할 패턴

      match_param: 일치 옵션

      • c: 대소문자 구분. 기본값
      • i: 대소문자 무시
      • n: dot(.)를 개행 문자와 일치시킴
      • m: 다중 행 모드
      • x: 검색 패턴의 공백문자 무시
      • inmx처럼 다수옵션 선택 가능
      SELECT FIRST, LAST
FROM EMPLOYEES
WHERE REGEXP_LIKE(FIRST, '^Ste(v|ph)en$');

      Ste로 시작하고 v나 ph 다음에 en으로 끝나는 행 ex) Stephen, Steven

    2. REGEXP_REPLACE(source_char, pattern[, replace_str[, position[, occurrence[, match_param]]]])

      source_char에서 일치한 pattern을 replace_str로 변경한 문자값 반환

      position: 검색 시작 위치. 기본값은 1

      occurrence: 패턴 일치 횟수. 기본값은 1

      SELECT PHONE, REGEXP_REPLACE(
  PHONE, 
  '([[:digit:]]{3})\.([[:digit]]{3})\.([[:digit]]{4})', 
  '(\1) \2-\3'
) AS C1
FROM EMPLOYEES;

      패턴은 숫자 3자리.숫자 3자리.숫자 4자리

      세번째 인자로 나온 것은 형식으로 (첫 번째 일치) 두 번째 일치-세 번째 일치

      PHONE C1
      650.121.2004 (650) 121-2004
      011.44.1344.429268 011.44.1344.429268

      두 번째 행은 패턴이 일치하지 않아 C1이 PHONE을 변형하지 않은 문자열 그대로 반환했다

  `SELECT REGEXP_REPLACE('1A2B3C4D', '\D')`

  1A2B3C4D에서 숫자를 제외한 모든 문자를 세번째 인자가 없으므로 제거하고

  (세번째 인자가 있었다면 세번째 인자로 바꿈.)

  남는 것을 반환한다 

  => 1234

  1. REGEXP_SUBSTR(source_char, pattern[, position[, occurrence[, match_param[, subexpr]]]])

    source_char에서 일치하는 pattern 반환

    subexpr: 서브 표현식(0은 전체, 1 이상은 서브 표현식. 기본값은 0. 1 이상이면 ()로 감싸진 것 중에 일치하는 n번째 표현식을 반환하라는 것)

    REGEXP_SUBSTR('http://www.example.com/products', 'http://([[:alnum:]]+\.?)') => http://www.example.com/

    REGEXP_SUBSTR('1234567890', '(123)(4(56)(78))', 1, 1, 'i', 1') => 123

    REGEXP_SUBSTR('1234567890', '(123)(4(56)(78))', 1, 1, 'i', 4') => 78

    SELECT REGEXP_SUBSTR('AABABCABCD', '(AB)C\1') FROM DUAL;

    => ABCAB

    역 참조(\)를 활용해 일치하는 서브 표현식의 재참조가 가능하다

  `SELECT REGEXP_SUBSTR('http://www.abc.com/efg', '([^:/]+)', 1, 2) AS C1 FROM DUAL;`

  `:`나 `/`를 포함하지 않는 문자 뭉탱이를 전체 문자열(http~efg)에서 찾는데,

  첫번째 문자부터(기본값이 1인데 여기서는 세번째 인자로 직접 지정해줌)

  두번째 일치한 것을(네번째 인자) 반환하라

  => 첫번째 일치한 `http` 건너뛰고

  `://` 건너뛰고

  두번째 일치한 `www.abc.com`반환한다

  1. REGEXP_INSTR(source_char, pattern[, position[, occurrence[, return_opt[, match_param[, subexpr]]]])

    source_char에서 일치한 pattern의 시작 위치를 정수로 반환

    return_opt: 반환 옵션. 0은 시작 위치로 기본값. 1은 다음 위치

    REGEXP_INSTR('1234567890', '(123)(4(56)(78))', 1, 1, 0, 'i', 1) => 1

    REGEXP_INSTR('1234567890', '(123)(4(56)(78))', 1, 1, 0, 'i', 2) => 4

    REGEXP_INSTR('1234567890', '(123)(4(56)(78))', 1, 1, 0, 'i', 4) => 7

  2. REGEXP_COUNT(source_char, pattern[, position[, match_param]])

    source_char에서 일치한 pattern의 횟수 반환

    REGEXP_COUNT('123123123123123', '123', 1) => 5

    REGEXP_COUNT('123123123123', '123', 3) => 3