[Oracle] 언더바 포함 문자 검색하는 SQL 조건문 (ESCAPE)

사용자 아이디와 같이 '_'(언더바) 특수문자를 포함한 데이터를 찾아야할 때가 있다.

 

보통 SQL에서 문자열 검색은 WHERE 조건에 LIKE 연산자와 와일드카드(%,_)를 사용하지만,

검색 문자열에 '_' 언더바 문자가 포함되어 있을 경우, 원하는 결과가 제대로 나오지 않을 것이다.

 

 

---

잘못된 예시

 

예를 들어 'admin_' 문자열을 포함한 관리자 ID를 검색하고자 아래 SQL문을 실행한다고 해보자.

원하는 데이터 외에 다른 데이터도 결과에 포함되는 것을 볼 수 있다. 

 

SELECT *
  FROM USER_INFO
 WHERE USER_ID LIKE '%admin_%'

 

 

실행 결과)

USER_ID	USER_NAME
admin_1	관리자1
admin_2	관리자2
admin123	관리자
admini	관리자

 

---

 

SQL에서 언더바를 문자가 아니라 와일드카드로 인식했기 때문이다. 

언더바 와일드카드는 검색 문자의 갯수와 위치를 고정하고자 할 때 사용한다. 

따라서 위 SQL에서는 'admin' 문자 뒤에 문자 '하나'가 붙은 모든 데이터를 추출한다.

공식문서 내용 中)
An underscore (_) in the pattern matches exactly one character (as opposed to one byte in a multibyte character set) in the value.

 

 

'admin_1', 'admin_2' 처럼 언더바를 포함한 데이터 뿐만 아니라

'admin1', 'admin123', 'admini' 처럼 admin 뒤에 언더바가 아닌 문자가 붙은 데이터도 함께 결과로 가져오는 것이다. 

'admin' 데이터는 admin 뒤에 '_' 언더바에 해당하는 문자가 없기 때문에 실행 결과에 포함되지 않는다.

 

 

 

 

---

ESCAPE 절 

 

언더바를 와일드카드가 아닌 문자로 인식하게 하기 위해 ESCAPE 절을 사용할 수 있다.

escape character를 검색 문자열 내 와일드카드(%,_) 문자 앞에 배치하면 오라클이 와일드카드가 아니라 문자 그대로 인식한다. 

공식문서 내용 中)
You can include the actual characters % or _ in the pattern by using the ESCAPE clause, which identifies the escape character. If the escape character precedes the character % or _ in the pattern, then Oracle interprets this character literally in the pattern rather than as a special pattern-matching character. You can also search for the escape character itself by repeating it. For example, if @ is the escape character, then you can use @@ to search for @.

 

 

 

 

ESCAPE 사용 예시

 

검색 문자열 내 와일드카드 앞에 원하는 ESCAPE 문자를 넣고 LIKE 연산자 뒤에 ESCAPE 문자를 선언한다.

SELECT *
  FROM USER_INFO
 WHERE USER_ID LIKE '%admin\_%' ESCAPE '\';

 

실행 결과)

USER_ID	USER_NAME
admin_1	관리자1
admin_2	관리자2

 

 

---

escape character는 '\' 백슬래시가 아닌 '@', '#' 등 다른 문자들도 가능하다. (영문, 숫자도 가능)

ex) ESCAPE '#',  ESCAPE '$',  ESCAPE 'k' ...

SELECT *
  FROM USER_INFO
 WHERE USER_ID LIKE '%admin@_%' ESCAPE '@';

 

 

 

 

참고로 ESCAPE 문자는 1개만 설정 가능하다.

여러 ESCAPE 문자를 설정할 경우, 아래와 같은 에러가 발생한다.

SELECT * FROM USER_INFO WHERE USER_ID LIKE '%admin\_%' ESCAPE '@,\' ;

ORA-01425: escape character must be character string of length 1 01425. 00000 - "escape character must be character string of length 1" *Cause: Given escape character for LIKE is not a character string of length 1. *Action: Change it to a character string of length 1.

 

 

 

 

 

---

escape character 포함 문자열 만들기(Java)

 

<!-- SELECT 문 (Mybatis) -->
<select id="searchUsersByUserId" parameterType="java.lang.String" resultType="com.example.model.User">
  SELECT * 
    FROM user_info
   WHERE user_id LIKE '%' || #{userId} || '%' ESCAPE '\'
</select>

 

public static String makeSelectStr(String input) {
        StringBuilder builder = new StringBuilder();

        for (int i = 0; i < input.length(); i++) {
            char currentChar = input.charAt(i);
            if (currentChar == '_') {
                builder.append('\\'); //백슬래시를 문자로 인식하기 위해 두 번 입력
            }
            builder.append(currentChar);
        }

        return builder.toString();
}

 

 

 

 

 

 

 

---

참고👀

• https://docs.oracle.com/cd/B13789_01/server.101/b10759/conditions016.htm

LIKE

 

• https://keepgoing0328.tistory.com/entry/like-%EC%A0%88-%EC%A3%BC%EC%9D%98%ED%95%A0-%EC%A0%90

like 절 주의할 점