BEGIN
declare returnStr varchar(100);
declare cnt int;
declare i int;
declare j int;
declare tmpStr varchar(10);
if str is null then
return '';
end if;
set cnt = CHAR_LENGTH(str);
set i = 1;
set j = 1;
while i <=cnt DO
set tmpStr = substring(str,i,j);
set returnStr = concat(ifnull(returnStr,''),
case when tmpStr rlike '^(ㄱ|ㄲ)' OR ( tmpStr >= '가' AND tmpStr < '나' ) then 'ㄱ'
when tmpStr rlike '^ㄴ' OR ( tmpStr >= '나' AND tmpStr < '다' ) then 'ㄴ'
when tmpStr rlike '^(ㄷ|ㄸ)' OR ( tmpStr >= '다' AND tmpStr < '라' ) then 'ㄷ'
when tmpStr rlike '^ㄹ' OR ( tmpStr >= '라' AND tmpStr < '마' ) then 'ㄹ'
when tmpStr rlike '^ㅁ' OR ( tmpStr >= '마' AND tmpStr < '바' ) then 'ㅁ'
when tmpStr rlike '^ㅂ' OR ( tmpStr >= '바' AND tmpStr < '사' ) then 'ㅂ'
when tmpStr rlike '^(ㅅ|ㅆ)' OR ( tmpStr >= '사' AND tmpStr < '아' ) then 'ㅅ'
when tmpStr rlike '^ㅇ' OR ( tmpStr >= '아' AND tmpStr < '자' ) then 'ㅇ'
when tmpStr rlike '^(ㅈ|ㅉ)' OR ( tmpStr >= '자' AND tmpStr < '차' ) then 'ㅈ'
when tmpStr rlike '^ㅊ' OR ( tmpStr >= '차' AND tmpStr < '카' ) then 'ㅊ'
when tmpStr rlike '^ㅋ' OR ( tmpStr >= '카' AND tmpStr < '타' ) then 'ㅋ'
when tmpStr rlike '^ㅌ' OR ( tmpStr >= '타' AND tmpStr < '파' ) then 'ㅌ'
when tmpStr rlike '^ㅍ' OR ( tmpStr >= '하' AND tmpStr <= '히' ) then 'ㅎ'
else tmpStr end);
set i=i+1;
end while;
RETURN returnStr;
END
초성 검색을 검색값을 바꿀 생각을 했으나
반대로 결과값을 초성으로 변경해놓고 like 검색하여 처리.
--search_word ex) ㄱㄴㄷ
where fn_initial_search(result_title) LIKE '%search_word%';
결과가 될 컬럼을 함수로 초성으로 변경한뒤에 찾는다.
단점은 저렇게 찾으면 index 를 타지 않는다. 대량의 검색은 어울리지 않겠다.
'DB > MYSQL' 카테고리의 다른 글
MYSQL TO VO (0) | 2021.05.04 |
---|---|
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements (0) | 2021.03.03 |
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement. (0) | 2021.03.03 |
mysql backup (0) | 2021.03.03 |
mysql 5.7 install (0) | 2021.03.03 |