database.sarang.net
UserID
Passwd
Database
DBMS
MySQL
PostgreSQL
Firebird
ㆍOracle
Informix
Sybase
MS-SQL
DB2
Cache
CUBRID
LDAP
ALTIBASE
Tibero
DB 문서들
스터디
Community
공지사항
자유게시판
구인|구직
DSN 갤러리
도움주신분들
Admin
운영게시판
최근게시물
Oracle Q&A 41518 게시물 읽기
No. 41518
한글 <ㅡ> 영문 변환 관련 질문입니다.
작성자
허광민(hgm0302)
작성일
2018-01-29 17:22:25ⓒ
2018-01-29 17:23:35ⓜ
조회수
1,202

 안녕하세요.

오라클에서  '한국' 을  'gksrnr' 이렇게 변환하거나 'gksrnr' 을 '한국' 이렇게 변환 할 수 있는 방법이 있는지 알고 싶습니다.

이 글에 대한 댓글이 총 1건 있습니다.

조합형 한글 대상으로 만들어 봤습니다.
완성되지 않은 글자는 안됩니다.(자음만 또는 모음만 있는글자)


WITH t1 AS
(
-- 초성(588) --
SELECT 'ㄱ' k1, 'r' e1, 0 n1 FROM dual
UNION ALL SELECT 'ㄲ', 'R',  1 FROM dual
UNION ALL SELECT 'ㄴ', 's',  2 FROM dual
UNION ALL SELECT 'ㄷ', 'e',  3 FROM dual
UNION ALL SELECT 'ㄸ', 'E',  4 FROM dual
UNION ALL SELECT 'ㄹ', 'f',  5 FROM dual
UNION ALL SELECT 'ㅁ', 'a',  6 FROM dual
UNION ALL SELECT 'ㅂ', 'q',  7 FROM dual
UNION ALL SELECT 'ㅃ', 'Q',  8 FROM dual
UNION ALL SELECT 'ㅅ', 't',  9 FROM dual
UNION ALL SELECT 'ㅆ', 'T', 10 FROM dual
UNION ALL SELECT 'ㅇ', 'd', 11 FROM dual
UNION ALL SELECT 'ㅈ', 'w', 12 FROM dual
UNION ALL SELECT 'ㅉ', 'W', 13 FROM dual
UNION ALL SELECT 'ㅊ', 'c', 14 FROM dual
UNION ALL SELECT 'ㅋ', 'z', 15 FROM dual
UNION ALL SELECT 'ㅌ', 'x', 16 FROM dual
UNION ALL SELECT 'ㅍ', 'v', 17 FROM dual
UNION ALL SELECT 'ㅎ', 'g', 18 FROM dual
)
, t2 AS
(
-- 중성(모음,28) --
SELECT 'ㅏ' k2, 'k' e2, 0 n2 FROM dual
UNION ALL SELECT 'ㅐ', 'o' ,  1 FROM dual
UNION ALL SELECT 'ㅑ', 'i' ,  2 FROM dual
UNION ALL SELECT 'ㅒ', 'O' ,  3 FROM dual
UNION ALL SELECT 'ㅓ', 'j' ,  4 FROM dual
UNION ALL SELECT 'ㅔ', 'p' ,  5 FROM dual
UNION ALL SELECT 'ㅕ', 'u' ,  6 FROM dual
UNION ALL SELECT 'ㅖ', 'P' ,  7 FROM dual
UNION ALL SELECT 'ㅗ', 'h' ,  8 FROM dual
UNION ALL SELECT 'ㅘ', 'hk',  9 FROM dual
UNION ALL SELECT 'ㅙ', 'ho', 10 FROM dual
UNION ALL SELECT 'ㅚ', 'hl', 11 FROM dual
UNION ALL SELECT 'ㅛ', 'y' , 12 FROM dual
UNION ALL SELECT 'ㅜ', 'n' , 13 FROM dual
UNION ALL SELECT 'ㅝ', 'nj', 14 FROM dual
UNION ALL SELECT 'ㅞ', 'np', 15 FROM dual
UNION ALL SELECT 'ㅟ', 'nl', 16 FROM dual
UNION ALL SELECT 'ㅠ', 'b' , 17 FROM dual
UNION ALL SELECT 'ㅡ', 'm' , 18 FROM dual
UNION ALL SELECT 'ㅢ', 'ml', 19 FROM dual
UNION ALL SELECT 'ㅣ', 'l' , 20 FROM dual
)
, t3 AS
(
-- 종성(1) --
SELECT '' k3, '' e3, 0 n3 FROM dual
UNION ALL SELECT 'ㄱ', 'r' ,  1 FROM dual
UNION ALL SELECT 'ㄲ', 'R' ,  2 FROM dual
UNION ALL SELECT 'ㄳ', 'rt',  3 FROM dual
UNION ALL SELECT 'ㄴ', 's' ,  4 FROM dual
UNION ALL SELECT 'ㄵ', 'sw',  5 FROM dual
UNION ALL SELECT 'ㄶ', 'sg',  6 FROM dual
UNION ALL SELECT 'ㄷ', 'e' ,  7 FROM dual
UNION ALL SELECT 'ㄹ', 'f' ,  8 FROM dual
UNION ALL SELECT 'ㄺ', 'fr',  9 FROM dual
UNION ALL SELECT 'ㄻ', 'fa', 10 FROM dual
UNION ALL SELECT 'ㄽ', 'ft', 11 FROM dual
UNION ALL SELECT 'ㄼ', 'fq', 12 FROM dual
UNION ALL SELECT 'ㄾ', 'fx', 13 FROM dual
UNION ALL SELECT 'ㄿ', 'fv', 14 FROM dual
UNION ALL SELECT 'ㅀ', 'fg', 15 FROM dual
UNION ALL SELECT 'ㅁ', 'a' , 16 FROM dual
UNION ALL SELECT 'ㅂ', 'q' , 17 FROM dual
UNION ALL SELECT 'ㅄ', 'qt', 18 FROM dual
UNION ALL SELECT 'ㅅ', 't' , 19 FROM dual
UNION ALL SELECT 'ㅆ', 'T' , 20 FROM dual
UNION ALL SELECT 'ㅇ', 'd' , 21 FROM dual
UNION ALL SELECT 'ㅈ', 'w' , 22 FROM dual
UNION ALL SELECT 'ㅊ', 'c' , 23 FROM dual
UNION ALL SELECT 'ㅋ', 'z' , 24 FROM dual
UNION ALL SELECT 'ㅌ', 'x' , 25 FROM dual
UNION ALL SELECT 'ㅍ', 'v' , 26 FROM dual
UNION ALL SELECT 'ㅎ', 'g' , 27 FROM dual
)
-- 1. 'gksrnr' --> '한국'
SELECT LISTAGG(TO_CHAR(v)) WITHIN GROUP(ORDER BY gb)
  FROM (
        SELECT gb
             , NCHR(SUM(n)) v
          FROM (SELECT lv
                     , x
                     , SUM(DECODE(x, 1, 1)) OVER(ORDER BY lv) gb
                     , DECODE(x, 1, 44032 + n1 * 588
                               , 2, n2 * 28
                               , 3, n3
                               ) n
                  FROM (SELECT lv
                             , CASE WHEN n2 IS NOT NULL AND  LAG(n2) OVER(ORDER BY lv) IS     NULL THEN 2
                                    WHEN n2 IS NOT NULL AND  LAG(n2) OVER(ORDER BY lv) IS NOT NULL THEN 0
                                    WHEN n2 IS     NULL AND LEAD(n2) OVER(ORDER BY lv) IS NOT NULL THEN 1
                                    WHEN n2 IS     NULL AND  LAG(n2) OVER(ORDER BY lv) IS     NULL
                                                        AND LEAD(n2) OVER(ORDER BY lv) IS     NULL THEN 0
                                    ELSE 3
                                END x
                             , CASE WHEN n2 IS NOT NULL AND LEAD(n2) OVER(ORDER BY lv) IS NOT NULL THEN v2
                                    WHEN n2 IS     NULL AND LEAD(n2, 1) OVER(ORDER BY lv)  IS NULL
                                                        AND LEAD(n2, 2) OVER(ORDER BY lv)  IS NULL THEN v2
                                    ELSE v1
                                END v
                          FROM (SELECT LEVEL lv
                                     , SUBSTR(v, LEVEL, 1) v1
                                     , SUBSTR(v, LEVEL, 2) v2
                                  FROM (SELECT 'gksrnr' v FROM dual)
                                 CONNECT BY LEVEL <= LENGTH(v)
                                ) a
                             , t2 b
                         WHERE a.v1 = b.e2(+)
                        ) a
                      , t1
                      , t2
                      , t3
                 WHERE DECODE(x, 1, a.v) = t1.e1(+)
                   AND DECODE(x, 2, a.v) = t2.e2(+)
                   AND DECODE(x, 3, a.v) = t3.e3(+)
                )
         GROUP BY gb
        )
;
2. '똠방각하' --> 'Ehaqkdrkrgk'
SELECT LISTAGG(e1 || e2 || e3) WITHIN GROUP(ORDER BY lv) v
  FROM (SELECT LEVEL lv
             , SUBSTR(v, LEVEL, 1) v
             , ASCII(UNISTR(SUBSTR(v, LEVEL, 1))) - 44032 n
          FROM (SELECT '똠방각하' v FROM dual) x
         CONNECT BY LEVEL <= LENGTH(v)
        ) a
     , t1
     , t2
     , t3
 WHERE t1.n1(+) = FLOOR(n / 588)
   AND t2.n2(+) = FLOOR(MOD(n, 588) / 28)
   AND t3.n3(+) = MOD(MOD(n, 588) , 28)
;

마농(manon94)님이 2018-01-30 18:37:18에 작성한 댓글입니다.
이 댓글은 2018-01-30 18:38:21에 마지막으로 수정되었습니다.
[Top]
No.
제목
작성자
작성일
조회
41521insert시 점점 느려지는 현상
황의중
2018-02-01
1051
41520하단 합계값에 대한 비율을 구하고 싶습니다. [1]
봉달이
2018-01-31
1215
41519도와 주세요.. 제발.. 쿼리 문제 입니다. [3]
도와주세요..제발
2018-01-31
1187
41518한글 <ㅡ> 영문 변환 관련 질문입니다. [1]
허광민
2018-01-29
1202
41517저번에 마농님이 봐 주신 sql 입니다. [1]
봉달이
2018-01-26
1247
41516oracle client12c -> Linux redhat 7.4에 설치시 오류 [1]
oracle
2018-01-24
1011
41515표형태 쿼리 도움좀 주세요 [20]
김명찬
2018-01-23
1351
Valid XHTML 1.0!
All about the DATABASE... Copyleft 1999-2018 DSN, All rights reserved.
작업시간: 0.070초, 이곳 서비스는
	PostgreSQL v10.4로 자료를 관리합니다