oracle 초성검색 function(mssql function 수정)

MSSQL 쿼리를 MYSQL 로 만들어 수정해보았고 이걸 다시 ORACLE 용으로 변경해보았다..

 

좋네..ㅎ

 

 

----------------------------------------------------------------------------

 

오라클 초성 검색 펑션

 

 

CREATE OR REPLACE
FUNCTION FN_GET_CHOSUNG (STR IN  VARCHAR2)
RETURN VARCHAR2 IS
/******************************************************************************
   NAME        : FN_GET_CHOSUNG
   STR         : 홍길동
   RETURN      : ㅎㄱㄷ
******************************************************************************/

I INT;
J INT;
TMPSTR VARCHAR2(255);
COL1 VARCHAR2(2);

BEGIN
 I := LENGTH(STR);
 J := 1;
 TMPSTR := '';

 WHILE J <= I LOOP
  COL1 := SUBSTR(STR, J, 1);
  TMPSTR := TMPSTR ||
    (
      CASE WHEN COL1 < 'ㄱ' THEN COL1 
      WHEN ascii('ㄱ') <= ascii(COL1) and 
         ascii(COL1)<= ascii('ㅎ') THEN COL1 
      WHEN COL1 < '까' THEN 'ㄱ'
      WHEN COL1 < '나' THEN 'ㄲ'
      WHEN COL1 < '다' THEN 'ㄴ'
      WHEN COL1 < '따' THEN 'ㄷ'
      WHEN COL1 < '라' THEN 'ㄸ'
      WHEN COL1 < '마' THEN 'ㄹ'
      WHEN COL1 < '바' THEN 'ㅁ'
      WHEN COL1 < '빠' THEN 'ㅂ'
      WHEN COL1 < '사' THEN 'ㅃ'
      WHEN COL1 < '싸' THEN 'ㅅ'
      WHEN COL1 < '아' THEN 'ㅆ'
      WHEN COL1 < '자' THEN 'ㅇ'
      WHEN COL1 < '짜' THEN 'ㅈ'
      WHEN COL1 < '차' THEN 'ㅉ'
      WHEN COL1 < '카' THEN 'ㅊ'
      WHEN COL1 < '타' THEN 'ㅋ'
      WHEN COL1 < '파' THEN 'ㅌ'
      WHEN COL1 < '하' THEN 'ㅍ'
      ELSE 'ㅎ'
      END
   );
  J := J + 1;
 END LOOP;

 RETURN TMPSTR;
END;
----------------------------------------------------------------------------

 

 

MYSQL

 

CREATE FUNCTION `chosung`(STR VARCHAR(255)) RETURNS varchar(255) CHARSET utf8
    DETERMINISTIC
BEGIN
 DECLARE I INT;
 DECLARE J INT;
 DECLARE TMPSTR VARCHAR(255);
 DECLARE COL1 VARCHAR(2);

 SET I = char_length(STR);
 SET J = 1;
 SET TMPSTR = '';
  WHILE J <=I  DO
    SET COL1 = SUBSTRING(STR, J, 1);
  SET TMPSTR = CONCAT(TMPSTR,(
                       CASE WHEN COL1 < 'ㄱ' THEN COL1 
                       WHEN ascii('ㄱ') <= ascii(COL1) and 
                              ascii(COL1)<= ascii('ㅎ') THEN COL1 
                       WHEN COL1 < '까' THEN 'ㄱ'
                       WHEN COL1 < '나' THEN 'ㄲ'
                       WHEN COL1 < '다' THEN 'ㄴ'
                       WHEN COL1 < '따' THEN 'ㄷ'
                       WHEN COL1 < '라' THEN 'ㄸ'
                       WHEN COL1 < '마' THEN 'ㄹ'
                       WHEN COL1 < '바' THEN 'ㅁ'
                       WHEN COL1 < '빠' THEN 'ㅂ'
                       WHEN COL1 < '사' THEN 'ㅃ'
                       WHEN COL1 < '싸' THEN 'ㅅ'
                       WHEN COL1 < '아' THEN 'ㅆ'
                       WHEN COL1 < '자' THEN 'ㅇ'
                       WHEN COL1 < '짜' THEN 'ㅈ'
                       WHEN COL1 < '차' THEN 'ㅉ'
                       WHEN COL1 < '카' THEN 'ㅊ'
                       WHEN COL1 < '타' THEN 'ㅋ'
                       WHEN COL1 < '파' THEN 'ㅌ'
                       WHEN COL1 < '하' THEN 'ㅍ'
                       WHEN COL1 <= '힣' THEN 'ㅎ'
                       ELSE COL1
                       END
         ));
      SET J = J + 1 ;
  END WHILE;
  RETURN TMPSTR;
END

 

----------------------------------------------------------------------------

 

 

 

 

 

MYSQL : http://cjbox.tistory.com/111