한글 초성을 리턴해주는 함수

CREATE OR REPLACE FUNCTION f_get_hanterm (han IN VARCHAR2)
   RETURN VARCHAR2
   DETERMINISTIC -- 함수인덱스를 걸기 위함
IS
   hanterm   VARCHAR2 (50);
BEGIN
   SELECT CASE
             WHEN han < 'ㄱ'
                THEN substr(han, 1, 1)
             WHEN ASCII ('ㄱ') <= ASCII (han)
             AND ASCII (han) <= ASCII ('ㅎ')
                THEN han
             WHEN han < '나'
                THEN 'ㄱ'
             WHEN han < '다'
                THEN 'ㄴ'
             WHEN han < '라'
                THEN 'ㄷ'
             WHEN han < '마'
                THEN 'ㄹ'
             WHEN han < '바'
                THEN 'ㅁ'
             WHEN han < '사'
                THEN 'ㅂ'
             WHEN han < '아'
                THEN 'ㅅ'
             WHEN han < '자'
                THEN 'ㅇ'
             WHEN han < '차'
                THEN 'ㅈ'
             WHEN han < '카'
                THEN 'ㅊ'
             WHEN han < '타'
                THEN 'ㅋ'
             WHEN han < '파'
                THEN 'ㅌ'
             WHEN han < '하'
                THEN 'ㅍ'
             ELSE 'ㅎ'
          END
     INTO hanterm
     FROM DUAL;

   IF hanterm IS NULL
   THEN 
      hanterm := '';
   END IF;

   RETURN hanterm;
END;
/

 

 

 

[출처 1] http://www.cyworld.com/sjcij/3117142

[출처 2] http://blog.naver.com/orapybubu?Redirect=Log&logNo=40092439547

(출처1의 쿼리를 기반으로 출처2의 DETERMINISTIC을 이용하여 함수를 만들었다.)

 

- write by 꼬마갱이 2010.02.16 -

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

..