긍정적 사고, 음식의 절제, 규칙적인 운동

Mysql 자음 초성 추출 함수, 자음으로 검색, 초성 검색

함수를 만들어서 추출해보자. 

CREATE DEFINER=`gn`@`%` FUNCTION `fn_choSearch`(`str` varchar(20)) RETURNS varchar(20) CHARSET utf8 
 
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 = length(str)/3; 
     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 'ㅎ' end); 
           set i=i+1; 
     end while; 
  RETURN returnStr; 
END;

 

자바스크립트로 검색어가 초성인지 아닌지 체크

var keyword = $("#keyword").val().toUpperCase(); 
var choKeyword = choHangul($("#keyword").val()); 
 
//초성검색 구분 
 
if (keyword!="" && choKeyword==""){ 
    choDiv = 'Y'; 
}else{ 
    choDiv = 'N'; 
} 

/* 초성추출 */ 
function choHangul(str) { 
  cho = ["ㄱ","ㄲ","ㄴ","ㄷ","ㄸ","ㄹ","ㅁ","ㅂ","ㅃ","ㅅ","ㅆ","ㅇ","ㅈ","ㅉ","ㅊ","ㅋ","ㅌ","ㅍ","ㅎ"]; 
  result = ""; 
  for(i=0;i<str.length;i++) { 
      code = str.charCodeAt(i)-44032; 
    if(code>-1 && code<11172) result += cho[Math.floor(code/588)]; 
  } 
  return result; 
} 

쿼리에서 초성 추출하자.

WHERE 1=1 
<isEqual prepend='AND' property="choDiv" compareValue="N" > 
    S1.STOP_NAME LIKE concat('%',#keyword#,'%') OR S1.STOP_ID LIKE concat('%',#keyword#,'%') 
</isEqual> 
<isEqual prepend='AND' property="choDiv" compareValue="Y" > 
    fn_choSearch(S1.stop_name) LIKE concat('%',#keyword#,'%') OR S1.STOP_ID LIKE concat('%',#keyword#,'%') 
</isEqual> 

 

choDiv 값이 N 일 경우 일반검색
choDiv 값이 Y 일 경우 위의 oracle함수를 사용하여 초성검색


예를 들어
이렇게 구현을 하면 'ㄱㄴㄷ' 으로 검색을 하면
가나다
간난단
갇낟닫
...
등 초성이 ㄱ,ㄴ,ㄷ 인 데이터가 검색된다.

출처 : https://aljjabaegi.tistory.com/220

switch (new Date().getDay()) {
  case 0:
    day = "Sunday";
    break;
  case 1:
    day = "Monday";
    break;
  case 2:
     day = "Tuesday";
    break;
  case 3:
    day = "Wednesday";
    break;
  case 4:
    day = "Thursday";
    break;
  case 5:
    day = "Friday";
    break;
  case 6:
    day = "Saturday";
}

[javascript] Switch case else

switch(expression) {
  case x:
    // code block
    break;
  case y:
    // code block
    break;
  default:
    // code block
}

jsp 현재 날짜, 일주일전 날짜, 한달 전 날짜 구하기.

currentCalendar.add 부분에 -값이 아닌 +값을 대입하면 현재 이후의 날짜를 구할 수 있지요.

 

<%@ page language="java" contentType="text/html; charset=utf-8" pageEncoding="utf-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<%@ page import="java.text.DecimalFormat" %>
<%@ page import="java.text.SimpleDateFormat" %>
<%@ page import="java.util.Calendar" %>

<%
    DecimalFormat df = new DecimalFormat("00");
    Calendar currentCalendar = Calendar.getInstance();

  //현재 날짜 구하기
    String strYear   = Integer.toString(currentCalendar.get(Calendar.YEAR));
    String strMonth  = df.format(currentCalendar.get(Calendar.MONTH) + 1);
    String strDay   = df.format(currentCalendar.get(Calendar.DATE));
    String strDate = strYear + strMonth + strDay;

  //일주일 전 날짜 구하기
    currentCalendar.add(currentCalendar.DATE, -7);
    String strYear7   = Integer.toString(currentCalendar.get(Calendar.YEAR));
    String strMonth7  = df.format(currentCalendar.get(Calendar.MONTH) + 1);
    String strDay7   = df.format(currentCalendar.get(Calendar.DATE));
    String strDate7 = strYear7 + strMonth7 + strDay7;

  //한달 전 날짜 구하기
    currentCalendar.add(currentCalendar.DATE, -24);
    String strYear31   = Integer.toString(currentCalendar.get(Calendar.YEAR));
    String strMonth31  = df.format(currentCalendar.get(Calendar.MONTH) + 1);
    String strDay31   = df.format(currentCalendar.get(Calendar.DATE));
    String strDate31 = strYear31 + strMonth31 + strDay31;
%>

<!-- 현재날짜 -->
<c:set var="nowdate" value='<%=strDate%>' />
<!-- 일주일전 -->
<c:set var="nowdate7" value='<%=strDate7%>' />
<!-- 한달전 -->
<c:set var="nowdate31" value='<%=strDate31%>' /> 

MYSQL 특수문자 치환, 특수문자 치환 찾기MYSQL 특수문자 치환, 특수문자 치환 찾기. CHAR()

SELECT char(63);
SELECT ASCII('?') FROM DUAL;
SELECT CONCAT('http://naver.com/Mobile.dm',char(63),'123') MOBILE_URL

*** Tibero 접속 
1.  SSH 접속
>  su – root

2. 유저 root에서 Tibero 로 변경
>  su - tibero

3.디비 호출
> export TB_SID=디비명

4. tbsql 아이디/비밀번호
> tbsql 아이디
  pw...

//----------------------------------