Mssql - like절 와일드카드('[]') 문자열로 인식시키고 검색하기

Mssql로 동작하는 프로젝트에서 게시판 검색 기능이 잘 되지 않는다는 확인 요청건이 들어왔다.

 

실제로 '[공지]' 라는 키워드로 검색을 해보니 원하는 결과가 나오지 않았다.

 

아래는 작성한 쿼리이다.

SELECT * FROM 테이블명
WHERE 컬럼 LIKE '%' + 검색어(@PARAM) + '%' 
OFFSET 0 ROW FETCH NEXT 5 ROW ONLY

간단한 like절 검색 쿼리인데, 여기에 [공지]를 검색하게되면 와일드카드가 적용되면서 아래와 같은 쿼리로 변경되어 동작된다.

 

SELECT * FROM 테이블명
WHERE 컬럼 LIKE '%[공지]%' 
OFFSET 0 ROW FETCH NEXT 5 ROW ONLY

like절에는 %%라는 와일드카드뿐만 아니라 []라는 와일드카드도 존재한다.

 

https://docs.microsoft.com/ko-kr/sql/t-sql/language-elements/wildcard-character-s-to-match-transact-sql?view=sql-server-ver15 

하나 이상의 문자를 찾는 [] 와일드카드 - SQL Server (Transact-SQL)

ms에서 설명하고 있듯이 [a-z][0-9]와 같이 사용하여 우편번호라던지 특정 키워드 글자가 있는것을 찾아오는 기능인데,

해당방식으로 찾기때문에 우리가 원하는 결과가 나오지 않는다.

 

 

 

 

[]와 같은 와일드카드 강제 문자열 처리하기

[와 ]의 키워드를 강제 문자열로 처리하기 위해 REPLACE함수로 강제 치환 처리를 추가하였다.

(프로시져를 생성해서 return함수를 사용해도 좋을 것 같다.)

 

아래는 사용한 결과물이다.

SELECT * FROM 테이블명
WHERE 컬럼 LIKE '%' + REPLACE(REPLACE(검색어(@PARAM),'[','\['),']','\]') + '%' ESCAPE '\'
OFFSET 0 ROW FETCH NEXT 5 ROW ONLY

 

1. [로 시작하는 문자가 존재하면 \+[ 조합으로 변경한다.

2. ]로 시작하는 문자가 존재하면 \+[ 조합으로 변경한다.

3. ESCAPE를 통해 \를 제외처리하여 와일드카드가 동작하지 않도록 변경하고 \[공지\]라는 문자열로 자동 처리가 되도록 한다.

 

이후 정상적으로 1개가 검색이 되는 것을 확인했다.

검색이 된다!

 

 

 

REPLACE 변환처리 함수 생성하여 적용하기

와일드카드인 [, ] 뿐만 아니라 %, ^ 등도 추가를 해야하면 매번 REPLACE항목이 늘어나서 아래처럼 변경될 것이다.

REPLACE(REPLACE(REPLACE(REPLACE(@str,'[','\['),']','\]'), '%', '\%'), '^', '\^')

이런 쿼리를 mybatis등에 정의해두면 가독성도 안좋을 뿐더러, 수정도 어렵고 추가 사항이 발생하면 여간 골치 아픈게 아니다.

 

이런 REPLACE 기능 자체를 함수로 만들어서 like절마다 함수로 치환하면 좀 더 가독성도 좋고, 유지보수도 쉽게 처리 할  수 있다.

 

- 함수 만들기

먼저 REPLACE 치환 처리를 해주는 함수를 생성한다.

CREATE FUNCTION SP_ESCAPE_PROC(@str VARCHAR(300))
RETURNS VARCHAR(400)
AS
BEGIN
	DECLARE @RESULT VARCHAR(400)
	SET @RESULT = REPLACE(REPLACE(@str,'[','\['),']','\]')
RETURN @RESULT
END;

SP_ESCAPE_PROC이라는 함수명으로 함수를 생성했다.

1개의 Input을 받고 1개의 문자열을 Output하도록 만들었다. (문자열(300자 제한) 하나를 받고, 문자열(400자 제한)을 리턴한다.)

 

SET부분에선 실제로 처리할 로직을 입력한다. 여기선 '[]' 만 치환하도록 하였다.

F5키등을 통해 실행한다.

정상적으로 생성되었다.

 

실제로 생성한 함수를 통해 사용한 쿼리이다.

SELECT * FROM 테이블명
WHERE 컬럼 LIKE '%' + dbo.SP_ESCAPE_PROC('[공지]') + '%' ESCAPE '\'
OFFSET 0 ROW FETCH NEXT 5 ROW ONLY

깔끔해지기도 했고, 추가사항이 생기면 함수를 수정해주면 된다.