Oracle) Project) 값 리스트를 특수문자로 구분해 변환한 문자열에서 특정 값의 순서를 구하는 방법 : REGEXP_INSTR(), REGEXP_COUNT()

특정 값들이 포함된 배열을 ,와 같은 특수문자로 구분하여 하나의 문자열로 변환해 사용하는 경우 빈번하다. 이때 특정 값이 이 문자열 내에서 몇번째 순서(즉, 배열 내 index값)인지 구해야 하는 경우가 있다.
이때 문자열을 다시 배열로 바꿔 특정 값의 index를 구하는 것이 아니라 DB 내에서 쿼리 작성을 통해 해결해야할 때가 있다.
-> Oracle의 문자열에서 특정 문자열의 index 반환해주는 REGEXP_INSTR() 함수와 문자열에서 특정 문자열의 개수를 반환해주는 REGEXP_COUNT() 함수 이용

• REGEXP_INSTR() https://lnsideout.tistory.com/entry/ORACLE-%EC%98%A4%EB%9D%BC%ED%81%B4-INSTR-%EB%AC%B8%EC%9E%90-%EC%9C%84%EC%B9%98-%EC%B0%BE%EA%B8%B0-%EB%B0%8F-%EC%98%88%EC%A0%9C
• REGEXP_COUNT https://gent.tistory.com/427

ex) 특정 영화에 출연한 배우의 정보를 배우의 비중에 따라 순서대로 배우명|배우영문명|극중역할,배우명|배우영문명|극중역할,... 과 같은 형식으로 저장한 문자열에서 특정 배우의 순서(중요도)를 숫자값으로 출력하고자 한다.

• REGEXP_INSTR('문자열','찾을문자열','시작위치','발생횟수')
  : '문자열'에서 '찾을문자열'의 위치를 '시작위치'에서부터 찾기 시작해 '발생횟수'번째 '찾을문자열'의 위치값을 반환

• REGEXP_COUNT('문자열','찾을문자열')
  : '문자열'에서 '찾을문자열'의 총 개수를 반환

  SELECT ft.peopleNm, ft.movieCd, ft.movieNm
      ,ft.commaCnt, ft.peopleIdx
      /* ---------------------------------------------------------- */
      ,ft.commaCnt - REGEXP_COUNT(ft.actorDetail, ',', ft.peopleIdx) +1 AS actorOrder
      /* ---------------------------------------------------------- */
      ,ft.actorDetail
  FROM (
      SELECT iv2.peopleNm, m.movieCd, m.movieNm
      	/* ---------------------------------------------------------- */
          , REGEXP_INSTR(m.actorDetail, iv2.peopleNm) AS peopleIdx
          , REGEXP_COUNT(m.actorDetail, ',') AS commaCnt
          /* ---------------------------------------------------------- */
          , m.actorDetail
      from (
          SELECT iv1.peopleCd, iv1.peopleNm, regexp_substr(iv1.actorFilmos, '[^,]+', 1, level) actorFilmo
          from (
              SELECT p.peopleCd, p.peopleNm, p.actorFilmos from tbl_people p where p.peoplecd= '10019067'
          ) iv1
          CONNECT BY regexp_substr(iv1.actorFilmos, '[^,]+', 1, level) IS NOT NULL
          /* CONNECT BY는 WHERE 이전에 실행되므로 FROM절이 단일행이 아니면 전체 FULL SCAN하여 연삭속도 극히느려짐 */
      ) iv2
      INNER JOIN tbl_movie m
      ON (iv2.actorFilmo = m.movieCd)
  ) ft
  WHERE ft.peopleIdx != 0 /* INSTR()은 찾고자 하는 문자열 없으면 0 반환 */
  ;

• 구하는 순서
  1) REGEXP_COUNT 함수를 이용해 특수문자(,)의 총 개수 구하기
  2) REGEXP_INSTR 함수 이용해 찾고자 하는 문자열의 위치 구하기
  3) REGEXP_COUNT 함수 이용해 2)에서 구한 찾고자하는 문자열의 위치부터 특수문자(,)의 개수 구하기
  4) 찾고자하는 문자열의 순서(1부터) : 특수문자 총 개수 - 찾고자하는 문자열의 위치부터의 특수문자 개수 + 1

시행착오)
동일한 값을 얻기 위해 이전엔 한 컬럼의 문자열을 특정 문자로 나누어 행으로 정렬해주는 REGEXP_SUBSTR() 함수를 두번 이용해 같은 과정 수행했었다...
똑같이 한 배우의 필모를 REGEXP_SUBSTR 함수로 정렬해 리스트 값 반환받고,
이를 다시 파라미터값으로 전달해 필모의 출연배우 컬럼을 REGEXP_SUBSTR 함수로 나누어 그때의 ROWNUM 값을 리턴받음..
REGEXP_SUBSTR 함수는 단일행 인라인뷰를 사용하지않으면 연산속도 극히 느려지므로 Service 계층에서 반복문 돌려서 한 영화마다 쿼리를 실행해야했다..)

<select id="getActorFilmoList" resultType="com.moviepedia.domain.FavoritePeopleDTO">
        SELECT ft.peopleNm, m.movieCd, m.actorDetail  /*ft.peopleCd , ft.actorFilmo, m.movieCd, */ 
        FROM (
            SELECT iv.peopleCd, iv.peopleNm, regexp_substr(iv.actorFilmos, '[^,]+', 1, level) actorFilmo
            FROM (
                SELECT p.peopleCd, p.peopleNm, p.actorFilmos FROM tbl_people p WHERE p.peoplecd= #{peopleCd}
            ) iv
            CONNECT BY REGEXP_SUBSTR(iv.actorFilmos, '[^,]+', 1, LEVEL) IS NOT NULL
            /* CONNECT BY는 WHERE 이전에 실행되므로 FROM절이 단일행이 아니면 전체 FULL SCAN하여 연삭속도 극히느려짐 */
        ) ft
        INNER JOIN tbl_movie m
        ON (ft.actorFilmo = m.movieCd)
    </select>

    <select id="getActorRoleImportanceValue" resultType="Integer">

        SELECT ft.rank AS actorRoleImportanceValue, ft.actorRole
        FROM (
            SELECT ROWNUM AS rank, REGEXP_SUBSTR(iv.actorDetail, '[^,]+', 1, LEVEL) AS actorRole
            FROM (
                SELECT m.actorDetail
                FROM tbl_movie m
                WHERE movieCd =#{movieCd}
            ) iv    
            CONNECT BY	REGEXP_SUBSTR(iv.actorDetail, '[^,]+', 1, LEVEL) IS NOT NULL
            ORDER BY rank ASC
        ) ft
        WHERE REGEXP_INSTR(ft.actorRole, #{peopleNm}) > 0 AND ROWNUM = 1

    </select>