[오라클팁] 특수문자['] 검색 및 삽입 방법

개발을 진행중에 콘텐츠 내용에 특수문자가 있어서 특정 특수문자를 검색해야 하는 경우가 생겼습니다. 검색구문에 ['] 같은 특수 문자가 들어갔기 때문이죠

보통 특수문자는 정규식 치환처리를 하여 입력을 하지만, 따로 변환처리가 되어있지않아 자료를 검색하기가 곤란한 경우였습니다.

테이블 이름은 TEST, 컬럼은 CONTENT라고 하겠습니다. 테이블을 만들어주시고 내용을 입력해봅니다.

CREATE test(

contet VARCHAR2(4000)

);

INSERT INTO test VALUES('1234'abc'456');

구문대로 적용하면 인서트에서 오류가 생길겁니다. 왜냐하면 싱글따옴표 사용이 잘못되었기 때문이죠. 싱글 따옴표는 문장의 처음과 끝을 완성해야 되는데 처음과 끝이 제대로 구성이 안되어있죠.

사용자가 [1234'abc'456] 이라는 내용을 입력하고 싶어도 ['] 때문에 입력이 안됩니다. 따라서 이런 특수문자를 처리하기 위한 방법을 소개해 드리겠습니다.

[']를 오라클에서 인식할 수 있는 아스키코드로 변환시켜 인식시키는 방법이 있습니다. 아시키코드는 공백문자나, 엔터, 스페이스, 각종 키값을 코드로 변환시켜 오라클에서 사용할 수 있습니다. 일단, 저희가 사용할 [']만 알아보겠습니다^^

아스키코드 변환 예제)

SELECT ascii('변환시킬특수문자') FROM dual ;

예제의 쿼리로 특수문자를 대입해서 아스키 코드 문자를 알아낸뒤 CHR('아스키코드') 형태로 치환해서 쓰는 방법입니다. 이렇게 캐릭터코드로 변환시키면 특수문자에 대한 구문오류가 나타나지 않기에 사용하실 수 있는거죠.

애석하게도 [']의 경우 위의 쿼리로 실행 할수 없고 미리 지정된 코드를 사용하시면 됩니다. [']의 경우 아스키코드 문자는 39이며 캐릭터 코드는 CHR(39)가 되죠!!

따라서 ['] = [CHR(39)]로 치환해서 쓰면 되며 예제는 아래와 같습니다.

INSERT INTO test VALUES('1234'||CHR(39)||'abc'||CHR(39)||'456');

문장과 코드를 연결하기 위해서 연결자[||] 를 사용하였습니다.

['1234'] 와 [CHR(39)] , ['abc'] ,[CHR(39)] 마지막으로 ['456'] 을 연결해서 1234'abc'456을 만들고 있습니다.

그럼 이걸 응용해서 검색도 해보도록 하죠.

SELECT * FROM test WHERE content like '%'||CHR(39)||'%'

이해 하시겠나요? 문장안에서 [']를 검색하는 쿼리입니다. 

이렇게 하면 쉽게 검색이 될것 같은데 문제가 생기는 특수문자가 있습니다. [%]는 like에서 전체검색의 조건으로 쓰이고 있는데 같은 문자이다 보니 검색이 전체 검색이 되어서 정확한 조건 검색이 되지를 않습니다. 따라서 escape 문자를 써서 검색을 해보도록하겠습니다.

SELECT * FROM test WHERE content like '%#%%' escape '#'

찾을 검색어 앞에 [#]을 붙이고 [#]을 검색에서 제외시키는 쿼리입니다. 이렇게하면 손쉽게 원하는 조건을 검색하실 수 있습니다.