한글 초중종성 검색방법(자음만 하는것도 가능)

오라클에서 한글 초중종성 검색을 구현하는 방법이다

저는 자음만 하고 싶어서 자음만 하는 방법도 있습니다.

1. 한글을 초중종성을  나눠주는 FUNCTION 생성

CREATE OR REPLACE FUNCTION FN_GET_KOREA_SYLLABLE (
    i_p1    IN    VARCHAR2
)
    RETURN VARCHAR2
AS
    l_rt    VARCHAR2 (4000);
 
 
    FUNCTION f1 (
        i_p1    IN    VARCHAR2
    )
        RETURN VARCHAR2
    AS
        l_p1    PLS_INTEGER     := ASCII (UNISTR (i_p1));
        l_i0    PLS_INTEGER     := l_p1 - TO_NUMBER ('AC00', 'XXXX');
        l_i1    PLS_INTEGER     := TO_NUMBER ('1100', 'XXXX') + FLOOR (l_i0 / (21 * 28));
        l_i2    PLS_INTEGER     := TO_NUMBER ('1161', 'XXXX') + FLOOR (MOD (l_i0, (21 * 28)) / 28);
        l_i3    PLS_INTEGER     := TO_NUMBER ('11A7', 'XXXX') + FLOOR (MOD (l_i0, 28));
        l_rt    VARCHAR2(4000);
    BEGIN
        CASE
            WHEN l_p1 BETWEEN TO_NUMBER ('AC00', 'XXXX') AND TO_NUMBER ('D7A3', 'XXXX')
            THEN
                l_rt := NCHR (l_i1)
                     || CASE WHEN l_i2 <> 4519 THEN NCHR (l_i2) END
                     || CASE WHEN l_i3 <> 4519 THEN NCHR (l_i3) END
                     ;
            ELSE
                l_rt := i_p1;
        END CASE;
 
        RETURN l_rt;
    END f1;
 
 
    FUNCTION f2 (
        i_p1    IN    VARCHAR2
    )
        RETURN VARCHAR2
    AS
        l_p1    VARCHAR2(10);
        l_rt    VARCHAR2(30);
    BEGIN
 
        FOR i IN 1.. LENGTH( i_p1 )
        LOOP
        
            l_p1 := SUBSTR(i_p1, i, 1);
        
            IF l_p1 = 'ᆨ' OR l_p1 = 'ᄀ' THEN l_p1 := 'ㄱ';
            ELSIF l_p1 = 'ᆫ' OR l_p1 = 'ᄂ' THEN l_p1 := 'ㄴ';
            ELSIF l_p1 = 'ᆮ' OR l_p1 = 'ᄃ' THEN l_p1 := 'ㄷ';
            ELSIF l_p1 = 'ᆯ' OR l_p1 = 'ᄅ' THEN l_p1 := 'ㄹ';
            ELSIF l_p1 = 'ᆷ' OR l_p1 = 'ᄆ' THEN l_p1 := 'ㅁ';
            ELSIF l_p1 = 'ᆸ' OR l_p1 = 'ᄇ' THEN l_p1 := 'ㅂ';
            ELSIF l_p1 = 'ᆺ' OR l_p1 = 'ᄉ' THEN l_p1 := 'ㅅ';
            ELSIF l_p1 = 'ᆼ' OR l_p1 = 'ᄋ' THEN l_p1 := 'ㅇ';
            ELSIF l_p1 = 'ᆽ' OR l_p1 = 'ᄌ' THEN l_p1 := 'ㅈ';
            ELSIF l_p1 = 'ᆾ' OR l_p1 = 'ᄎ' THEN l_p1 := 'ㅊ';
            ELSIF l_p1 = 'ᆿ' OR l_p1 = 'ᄏ' THEN l_p1 := 'ㅋ';
            ELSIF l_p1 = 'ᇀ' OR l_p1 = 'ᄐ' THEN l_p1 := 'ㅌ';
            ELSIF l_p1 = 'ᇁ' OR l_p1 = 'ᄑ' THEN l_p1 := 'ㅍ';
            ELSIF l_p1 = 'ᇂ' OR l_p1 = 'ᄒ' THEN l_p1 := 'ㅎ';
            ELSIF l_p1 = 'ᆩ' OR l_p1 = 'ᄁ' THEN l_p1 := 'ㄲ';
            ELSIF l_p1 = 'ᄄ' THEN l_p1 := 'ㄸ';
            ELSIF l_p1 = 'ᄈ' THEN l_p1 := 'ㅃ';
            ELSIF l_p1 = 'ᄊ' OR l_p1 = 'ᆻ' THEN l_p1 := 'ㅆ';
            ELSIF l_p1 = 'ᄍ' THEN l_p1 := 'ㅉ';
            ELSIF l_p1 = 'ᆪ' THEN l_p1 := 'ᄀᄉ';
            ELSIF l_p1 = 'ᆬ' THEN l_p1 := 'ᄂᄌ';
            ELSIF l_p1 = 'ᆭ' THEN l_p1 := 'ᄂᄒ';
            ELSIF l_p1 = 'ᆰ' THEN l_p1 := 'ᄅᄀ';
            ELSIF l_p1 = 'ᆱ' THEN l_p1 := 'ᄅᄆ';
            ELSIF l_p1 = 'ᆲ' THEN l_p1 := 'ᄅᄇ';
            ELSIF l_p1 = 'ᆳ' THEN l_p1 := 'ᄅᄉ';
            ELSIF l_p1 = 'ᆴ' THEN l_p1 := 'ᄅᄐ';
            ELSIF l_p1 = 'ᆶ' THEN l_p1 := 'ᄅᄒ';
            ELSIF l_p1 = 'ᆹ' THEN l_p1 := 'ᄇᄉ';
            ELSIF l_p1 = 'ᅡ' THEN l_p1 := 'ㅏ';
            ELSIF l_p1 = 'ᅣ' THEN l_p1 := 'ㅑ';
            ELSIF l_p1 = 'ᅥ' THEN l_p1 := 'ㅓ';
            ELSIF l_p1 = 'ᅧ' THEN l_p1 := 'ㅕ';
            ELSIF l_p1 = 'ᅩ' THEN l_p1 := 'ㅗ';
            ELSIF l_p1 = 'ㅛ' THEN l_p1 := 'ㅛ';
            ELSIF l_p1 = 'ᅮ' THEN l_p1 := 'ㅜ';
            ELSIF l_p1 = 'ᅲ' THEN l_p1 := 'ㅠ';
            ELSIF l_p1 = 'ᅳ' THEN l_p1 := 'ㅡ';
            ELSIF l_p1 = 'ᅵ' THEN l_p1 := 'ㅣ';
            ELSIF l_p1 = 'ᅢ' THEN l_p1 := 'ㅐ';
            ELSIF l_p1 = 'ᅤ' THEN l_p1 := 'ㅒ';
            ELSIF l_p1 = 'ᅦ' THEN l_p1 := 'ㅔ';
            ELSIF l_p1 = 'ᅨ' THEN l_p1 := 'ㅖ';
            ELSIF l_p1 = 'ᅬ' THEN l_p1 := 'ㅚ';
            ELSIF l_p1 = 'ᅱ' THEN l_p1 := 'ㅟ';
            ELSIF l_p1 = 'ᅴ' THEN l_p1 := 'ㅢ';
            ELSIF l_p1 = ' ' THEN l_p1 := '';
            END IF;
            
            l_rt := l_rt || l_p1;
        
        END LOOP;
 
        RETURN l_rt;
    END f2;
 
 
BEGIN
 
    FOR i IN 1..LENGTH( i_p1 )
    LOOP
        l_rt := l_rt || f2( f1( SUBSTR(i_p1, i, 1) ) );
    END LOOP;
 
    RETURN l_rt;
END FN_GET_KOREA_SYLLABLE;

 

사용 방법

한글자 찾을때

SELECT * FROM 테이블명1 WHERE FN_GET_KOREA_SYLLABLE(컬럼명) LIKE FN_GET_KOREA_SYLLABLE('%원하는글자%')

두글자 찾을때

SELECT * FROM 테이블명1 WHERE FN_GET_KOREA_SYLLABLE(컬럼명) LIKE FN_GET_KOREA_SYLLABLE('%원하는글자%'||'%원하는글자%')

예시

 

 

SELECT * FROM EMP WHERE FN_GET_KOREA_SYLLABLE(ENAME) LIKE FN_GET_KOREA_SYLLABLE('%ㅈ%'||'%ㄱ%'||'%ㄷ%');