한글초성검색

sql

닫기 줄바꿈

create or replace function fn_get_hangule_cho_name (vstr in varchar2)
return varchar2 as

str       varchar2(2000);
chostr    varchar2(2000);
ch        varchar2(10);
sch        varchar2(10);
ix        number;
maxlen    number;
--   /* *****************************************************************************
--   Description      : 한글초성 검색
​​​--------------------------------------------------------------------------------
--   Input Arguments  : vstr(한글)
--   Program Date     : start : 2012. 08. 28
--   Author              : CBW
--   Update History   :
--   ***************************************************************************** */
​​​v_rtn varchar2(2000):= '';

begin
​​chostr  := '';
​​str := replace(vstr, ' ','');
​​if nvl(str,'x') ='x' then
​​​​​return null;
​​end if;
​​maxlen := length(str);
​​for ix in 1..maxlen loop
​​​​--   sch := substr(str, ix, 1);
​​​​​​​select asciistr(substr(str, ix, 1))
​​​​​​​​​​​​​into ch    
​​​​​​​​from dual;
​​​​​​chostr := chostr || case when ch between '\D558' and '\D7A3' or ch ='\314E' then 'ㅎ'
​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​when ch between '\D30C' and '\D557' or ch ='\314D' then 'ㅍ'
​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​when ch between '\D0C0' and '\D30B' or ch ='\314C' then 'ㅌ'
​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​when ch between '\CE74' and '\D0BF' or ch ='\314B' then 'ㅋ'
​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​when ch between '\CC28' and '\CE73' or ch ='\314A' then 'ㅊ'
​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​when ch between '\C9DC' and '\CC27' or ch ='\3149' then 'ㅉ'
​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​when ch between '\C790' and '\C9DB' or ch ='\3148' then 'ㅈ'
​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​when ch between '\C544' and '\C78F' or ch ='\3147' then 'ㅇ'
​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​when ch between '\C2F8' and '\C543' or ch ='\3146' then 'ㅆ'
​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​when ch between '\C0AC' and '\C2F7' or ch ='\3145' then 'ㅅ'
​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​when ch between '\BE60' and '\C0AB' or ch ='\3143' then 'ㅃ'
​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​when ch between '\BC14' and '\BE5F' or ch ='\3142' then 'ㅂ'
​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​when ch between '\B9C8' and '\BC13' or ch ='\3141' then 'ㅁ'
​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​when ch between '\B77C' and '\B9C7' or ch ='\3139' then 'ㄹ'
​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​when ch between '\B530' and '\B77B' or ch ='\3138' then 'ㄸ'
​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​when ch between '\B2E4' and '\B52F' or ch ='\3137' then 'ㄷ'
​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​when ch between '\B098' and '\B2E3' or ch ='\3134' then 'ㄴ'
​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​when ch between '\AE4C' and '\B097' or ch ='\3132' then 'ㄲ'
​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​when ch between '\AC00' and '\AE4B' or ch ='\3131' then 'ㄱ'
​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​else ''
​​​​​​​​​​​​​​​​​​​​​​​​​​end;
​​end loop;
​​return trim(chostr);

exception
​​when others then
​​​​​​​dbms_output.put_line (sqlerrm);
​​​​​​​raise_application_error (-20021, sqlerrm);
end fn_get_hangule_cho_name;