[오라클] 검색어 자동완성을 위한 초성, 중성, 종성 분리해서 검색하는 oracle 함수

프로젝트를 하면서 검색어 자동완성을 작업할 일이 있어서 개발을 해봤습니다.

네이버나 구글에서 검색어를 입력하면 나오는 그 검색어 자동완성 말이죠.

바로 이건데요.

여기서 핵심은 초성, 중성, 종성 검색이 가능해야 한다는 것이었습니다.

즉, 'ㄱ'을 입력하면 'ㄱ'으로 시작하는 모든 게 다 나와야 합니다.

'날ㅆ'를 입력하면 '날씨, 날쏘' 등등이 나와야 하구요.

'백'으로 검색을 하면 '배경화면'같은 단어들도 나와야 합니다.

어떻게 하면 이런 게 가능할까? 고민하다가

검색할 데이터를 초성, 중성, 종성으로 나눠서 따로 저장해놓고

검색어도 초성, 중성, 종성으로 나눠서 검색을 하면 되겠다는 생각이 들더군요.

이렇게 DB에 저장할 때 실제 제목과, 검색을 위한 초/중/종성으로 분리한 제목 2가지로 저장을 했습니다.

그리고 나서 '백'으로 검색을 한다고 하면 'ㅂㅐㄱ'으로 검색을 하면 되는거죠.

SELECT CONTS_NM, CONTS_NM_DIV
FROM TB_CN_CONTS_BAS
WHERE CONTS_NM_DIV LIKE '%'|| FN_GET_DIV_KO_CHAR('ㅂㅐㄱ') ||'%'

이렇게 말이죠.

그럼, 이제 초/중/종성으로 한글을 분리해주는 오라클 함수만 하나 만들면 되는데요.

구글링을 하니 나오더라구요.

프로젝트에 맞게 조금 수정을 했는데 그 함수는 다음과 같습니다.

CREATE OR REPLACE FUNCTION FN_GET_DIV_KO_CHAR (
    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 := 'ㅢ';
            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_DIV_KO_CHAR;

자, 그럼 이 함수를 이용해서 쿼리를 한번 만들어 볼까요?

SELECT '123으로 나눴다', FN_GET_DIV_KO_CHAR('123으로 나눴다') FROM DUAL
union
SELECT 'Galaxy7 출시하다', FN_GET_DIV_KO_CHAR('Galaxy7 출시하다') FROM DUAL
union
SELECT '밝은 형광등', FN_GET_DIV_KO_CHAR('밝은 형광등') FROM DUAL
union
SELECT '흰자와 노른자를 분리하다', FN_GET_DIV_KO_CHAR('흰자와 노른자를 분리하다') FROM DUAL

제가 원하던대로 잘 되네요~

지금 하는 프로젝트에 반영을 해서 입력받은 검색어로 쿼리를 만들어 봤습니다.

SELECT CONTS_NM
FROM TB_CN_CONTS_BAS
WHERE CONTS_NM_DIV LIKE '%'|| FN_GET_DIV_KO_CHAR('백') ||'%'

와우~ 잘 됩니다^^

이제 자동완성 화면을 만들어야 겠네요.

이제 자바스크립트와의 싸움이 되겠네요 ㅋ