꿈꾸는 개발자, DBA 커뮤니티 구루비

오라클 정규식 관련 질문입니다.

by 문두 [SQL Query] [2013.08.08 00:23:36]

퀴즈겸.. 질문입니다.
 
WITH C_T AS
(
 SELECT '00001' AS ID, '[뭐뭐뭐] @ 님은 대출금 @ 이 @ 가능하십니다.' AS CTNT FROM DUAL UNION ALL
 SELECT '00002' AS ID, '[뭐뭐뭐] @ 님의 결제예정일은 @일 입니다.' AS CTNT FROM DUAL
),
P_T AS
(
 SELECT 'A000000001' AS CUST_NO, '홍길동' AS ADD_INF_CTNT, 1 AS LOC_INF FROM DUAL UNION ALL
 SELECT 'A000000001' AS CUST_NO, '150,000,000' AS ADD_INF_CTNT, 2 AS LOC_INF FROM DUAL UNION ALL
 SELECT 'A000000001' AS CUST_NO, '2.6%' AS ADD_INF_CTNT, 3 AS LOC_INF FROM DUAL UNION ALL
  
 SELECT 'A000000002' AS CUST_NO, '이순신' AS ADD_INF_CTNT, 1 AS LOC_INF FROM DUAL UNION ALL
 SELECT 'A000000002' AS CUST_NO, '200,000,000' AS ADD_INF_CTNT, 2 AS LOC_INF FROM DUAL UNION ALL
 SELECT 'A000000002' AS CUST_NO, '3.0%' AS ADD_INF_CTNT, 3 AS LOC_INF FROM DUAL UNION ALL
  
 SELECT 'A000000003' AS CUST_NO, '김개똥' AS ADD_INF_CTNT, 1 AS LOC_INF FROM DUAL UNION ALL
 SELECT 'A000000003' AS CUST_NO, '210,000,000' AS ADD_INF_CTNT, 2 AS LOC_INF FROM DUAL UNION ALL
 SELECT 'A000000003' AS CUST_NO, '3.0%' AS ADD_INF_CTNT, 3 AS LOC_INF FROM DUAL 
)
SELECT A.CTNT
 , B.ADD_INF_CTNT
 , B.LOC_INF
 , REGEXP_REPLACE(CTNT,'@', ADD_INF_CTNT, 1, LOC_INF)
 FROM C_T A
 , P_T B
 WHERE A.ID = '00001'
 
/*
원하는결과 : 
  
[뭐뭐뭐] 홍길동 님은 대출금 150,000,000 이 2.6% 가능하십니다.
[뭐뭐뭐] 이순신 님은 대출금 200,000,000 이 3.0% 가능하십니다.
[뭐뭐뭐] 김개똥 님은 대출금 210,000,000 이 3.0% 가능하십니다.
※LOC_INF은 @의 순서입니다.
*/
 
쉽게생각했는데 어렵네요 ㅠㅠ

by 아발란체 [2013.08.08 10:29:40]

--말씀 하신 결과와 동일하게 나오나 결제 예정일 내용까지 취하려면 많은 수정이 필요합니당 ~ 
SELECT
 CUST_NO,
 REGEXP_REPLACE(CTNT, '@( 님.*)@( 이.*)@', NM||'\1'||PRICE||'\2'||RATE)
FROM (
 SELECT
 CUST_NO,
 MAX(CTNT) AS CTNT,
 MAX(DECODE(LOC_INF, 1, ADD_INF_CTNT, '')) AS NM,
 MAX(DECODE(LOC_INF, 2, ADD_INF_CTNT, '')) AS PRICE,
 MAX(DECODE(LOC_INF, 3, ADD_INF_CTNT, '')) AS RATE
 FROM
 C_T A, P_T B
 WHERE
 A.ID = '00001'
 GROUP BY
 CUST_NO
)

by 마농 [2013.08.08 10:30:48]

WITH c_t AS
(
SELECT '00001' id, '[뭐뭐뭐] @ 님은 대출금 @ 이 @ 가능하십니다.' ctnt FROM dual
UNION ALL SELECT '00002', '[뭐뭐뭐] @ 님의 결제예정일은 @일 입니다.' FROM dual
)
, p_t AS
(
SELECT 'A000000001' cust_no, '00001' id, 1 loc_inf, '홍길동' add_inf_ctnt FROM dual
UNION ALL SELECT 'A000000001', '00001', 2, '150,000,000' FROM dual
UNION ALL SELECT 'A000000001', '00001', 3, '2.6%'        FROM dual
UNION ALL SELECT 'A000000002', '00001', 1, '이순신'      FROM dual
UNION ALL SELECT 'A000000002', '00001', 2, '200,000,000' FROM dual
UNION ALL SELECT 'A000000002', '00001', 3, '3.0%'        FROM dual
UNION ALL SELECT 'A000000003', '00001', 1, '김개똥'      FROM dual
UNION ALL SELECT 'A000000003', '00001', 2, '210,000,000' FROM dual
UNION ALL SELECT 'A000000003', '00001', 3, '3.0%'        FROM dual
UNION ALL SELECT 'A000000001', '00002', 1, '홍길동'      FROM dual
UNION ALL SELECT 'A000000001', '00002', 2, '2013.08.25'  FROM dual
UNION ALL SELECT 'A000000002', '00002', 1, '이순신'      FROM dual
UNION ALL SELECT 'A000000002', '00002', 2, '2013.09.01'  FROM dual
UNION ALL SELECT 'A000000003', '00002', 1, '김개똥'      FROM dual
UNION ALL SELECT 'A000000003', '00002', 2, '2013.08.31'  FROM dual
)
SELECT a.id
     , b.cust_no
     , REGEXP_SUBSTR(a.ctnt, '[^@]+')
    || LISTAGG(
       b.add_inf_ctnt || REGEXP_SUBSTR(a.ctnt, '[^@]+', 1, b.loc_inf + 1)
       , '') WITHIN GROUP(ORDER BY b.loc_inf) AS ctnt
  FROM c_t a
     , p_t b
 WHERE a.id = b.id
   AND a.id = '00001'
 GROUP BY a.id, b.cust_no, REGEXP_SUBSTR(a.ctnt, '[^@]+')
 ORDER BY a.id, b.cust_no
;

by 아발란체 [2013.08.08 10:38:15]

와... 정석이다..!
마농님 킹왕짱.

by 우리집아찌 [2013.08.08 14:05:02]

어렵네용..
근데 샘플 데이타가 매일 날라오는 문자랑 비슷하네요. ㅎㅎ

by 손님 [2013.08.08 11:23:23]

헐~역시마농님~ ㅜㅜ감사합니다.덕분에쉽게해결하겠네요^^추가로 utf8 디비에서 substrb 사용시 euckr기준으로 하는방법이있을까요?스마트폰으로작성중이라 힘드네용ㅜㅜ

by 마농 [2013.08.08 13:45:18]

-- SUBSTRB('오_라_클_클_럽', 1, 5)
-- 원하는 결과는 '오_라' 이자만 UTF8에서는 '오_ '이 나오게 된다.
SELECT v v0
     , SUBSTRB(v, 1, 5) v1
     , SUBSTRB(v, 1, 5 + LENGTHB(x) - LENGTH(x)) v2
  FROM (SELECT v
             , SUBSTRB(CONVERT(v, 'KO16MSWIN949'), 1, 5) x
          FROM (SELECT '오_라_클_클_럽' v FROM dual)
        )
;
-- v0 : '오_라_클_클_럽'
-- v1 : '오_ '
-- v2 : '오_라'

by 손님 [2013.08.08 15:07:21]

감탄 할 뿐입니다...^^

by 손님 [2013.08.08 15:21:19]

글쓴이입니다~다시한번감사드립니다~ 성능개선에큰도움이됬습니다 ㅜㅜ

댓글등록

SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code(

) 버튼을 클릭하여 작성 하시면 됩니다.

로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입

데이터베이스 질문과 답변