MSSQL : LIKE 구문 사용시 와일드카드 문자 Wildcard Characters 대처하는 방안 [번역]

MSSQL : LIKE 구문 사용시 와일드카드 문자 Wildcard Characters 대처하는 방안

 

문제

 

SQL Server LIKE 연산자를 사용하고 결과에 궁금하거나 특정 패턴과 일치시키기 위해 어떤 문자열을 사용해야 하는지 알고 싶으시다면 도움이 되실 겁니다.

 

아래 내용은 와일드카드 문자가 포함된 패턴을 사용하여 WHERE 절에 LIKE 연산자를 포함하는 SQL Server 쿼리를 작성하는 방법과 문자열 일치를 더 쉽게 만드는 데 사용할 수 있는 함수를 보여줍니다

 

해결책

 

LIKE 연산자를 사용하는 예와 예상치 못한 결과를 얻은 다음, 이 검색 패턴 문제를 해결하는 방법을 살펴보겠습니다.

 

 

SQL Server LIKE 연산자를 테스트하기 위한 샘플 데이터 생성

nvarchar, varchar, nchar 또는 char 데이터 유형과 INSERT 문자열이 있는 데이터베이스, 테이블, row 가 있는 테스트 환경을 만드는 것부터 시작하겠습니다.

 

USE master
GO

CREATE DATABASE TestDB
GO

USE TestDB
GO

CREATE TABLE myUser 
(
   LoginName NVARCHAR(50)
)

INSERT INTO myUser (LoginName) 
VALUES ('newUser'), 
       ('user1'),
       ('_myUser1'),
       ('myUser2'),
       ('_myUser3'),
       ('1MyUser4'),
       ('new%user'),
       ('%newuser%'),
       ('my[user]'),
       ('my[user]1'),
       ('myuser'),
       ('oldUser^/'),
       ('NewUser|'),
       ('User[5'),
       ('user]6')

 

따라서 myUser 테이블에는 다음과 같은 15개의 행이 존재합니다.

 

USE TestDB
GO

SELECT *
FROM myUser

 

 

 

간단한 SQL Server T-SQL LIKE 구문

WHERE 절에서 '_' 기호로 시작하는 모든 로그인을 찾고 싶을 경우,  LIKE 조건으로 SQL 문을 실행하기 전, 다음 쿼리의 결과를 확인해 보죠.

USE TestDB
GO

SELECT *
FROM myUser
WHERE LoginName LIKE '%_my%'

결과 내용에 대해 예상했던 것과 동일한지요?

 

 

'_'로 시작하지 않는데 '1MyUser4'가 포함되는 이유는 무엇입니까?

 

 

SQL Server T-SQL 와일드카드 문자

앞 단락의 마지막 질문에 대한 답은 밑줄('_')이 LIKE 절의 일반 문자가 아니라 와일드카드 문자라는 것입니다. 리터럴 문자와 달리 와일드카드 문자는 LIKE 연산자에 대해 특정한 의미를 갖습니다.

 

다음은 Microsoft 설명서에 따른 와일드카드 문자 목록입니다.

 

 

 

 

따라서 LIKE에서 밑줄('_')은 특정 일반 문자가 아니라 단일 문자를 의미합니다. 이것이 '1MyUser4'가 포함된 이유입니다. 또한 '_myUser1' 및 '_myUser3'은 행 및 패턴의 일치하는 밑줄(_) 이 아닙니다.

 

T-SQL에서 LIKE와 함께 와일드카드 문자 사용

 

대괄호로 묶인 와일드카드 문자는 패턴 일치를 위한 리터럴 문자로 간주되므로 올바른 결과를 얻기 위해 다음과 같은 방식으로 예제의 쿼리를 다시 작성할 수 있습니다.

USE TestDB
GO

SELECT *
FROM myUser
WHERE LoginName LIKE '%[_]my%'

 

 

아래 쿼리 경우, 쿼리 엔진에 '_my' 문자열로 시작하는 모든 로그인을 찾고자 합니다. 

 

 

아래 쿼리를 실행하면 대괄호([])로 묶인 LIKE 절에서 다른 와일드카드 문자를 사용할 때와 사용하지 않을 때의 차이점을 알 수 있습니다(마지막 쿼리에서는 대괄호 안에 '['를 포함할 필요가 없음).

 

USE TestDB
GO

-- %
SELECT *
FROM myUser
WHERE LoginName LIKE '%%%'

SELECT *
FROM myUser
WHERE LoginName LIKE '%[%]%'

-- [
SELECT *
FROM myUser
WHERE LoginName LIKE '%[%'

SELECT *
FROM myUser
WHERE LoginName LIKE '%[[]%'

-- ]
SELECT *
FROM myUser
WHERE LoginName LIKE '%]%'

 

그러나 일부 상황에서는 대괄호([]) 안에 와일드카드 문자를 포함하는 것이 편리하지 않습니다. 예를 들어 패턴에 많은 와일드카드 문자가 있거나 패턴을 매개변수로 받을 수 있습니다.

 

매개 변수를 사용하여 T-SQL에서 LIKE 값 저장

다음 예제에서는 변수를 선언하고 패턴으로 사용합니다.

USE TestDB
GO

DECLARE @myUser NVARCHAR(50) = '_my'

SELECT *
FROM myUser
WHERE LoginName LIKE '%'+ @myUser + '%'

 

결과는 '_'가 와일드카드 문자로 간주된 예와 동일합니다.

 

이 문제를 해결하기 위해서는 ESCAPE 절을 SQL LIKE 연산자와 함께 사용하여, 쿼리 엔진에 와일드카드 문자를 리터럴로 사용하도록 지시할 수 있습니다.

 

ESCAPE 절의 형식은 다음과 같습니다.

 

ESCAPE 'escape_character'

 

예를 들어 다음 쿼리에서 이스케이프 문자는 '!'이며 패턴에도 포함됩니다. 이것은 이스케이프 문자 다음에 오는 기호가 일반 기호로 간주되어야 함을 의미합니다.

 

USE TestDB
GO

SELECT *
FROM myUser
WHERE LoginName LIKE '%!_my%' ESCAPE '!'

 

따라서 '!' 뒤에 오는 '_' 기호는 패턴에서 는 와일드카드 문자로 간주되지 않으며 다음과 같은 결과가 나타납니다.

 

따라서 변수가 있는 예제에 ESCAPE 절을 적용한 후 올바른 결과를 얻었습니다.

 

USE TestDB
GO

DECLARE @myUser NVARCHAR(50) = '_my'

SELECT REPLACE(@myUser, '_','|_') AS 'AfterAddingEscapeCharacter'

SELECT *
FROM myUser
WWHERE LoginName LIKE '%' + REPLACE(@myUser, '_','|_') + '%' ESCAPE '|'

 

 

 

 

LIKE 이스케이프 절에 대한 SQL Server T-SQL 함수 만들기

루틴을 용이하게 하기 위해 ESCAPE 절과 함께 LIKE 연산자에서 사용할 문자열을 준비하는 함수를 만들 수 있습니다. 이 함수는 쿼리 결과에 영향을 줄 수 있는 모든 가능한 와일드카드 문자를 고려합니다.

 

 

USE TestDB
GO

CREATE FUNCTION udfReplaceWildcards(@myValue NVARCHAR(MAX))
RETURNS NVARCHAR(MAX)
AS
BEGIN

   /*-----------------------------------------------------------------------------------              
   @myValue - the value that will be used in the LIKE operator with an ESCAPE clause
   -----------------------------------------------------------------------------------*/

   --Replacing wildcard characters
   SET @myValue = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@myValue, '|','||'),'%','|%'),'_','|_'),'[','|['),']','|]')
   
   RETURN IsNull(@myValue,'')
END

 

이 함수에서는 '|'를 사용했습니다. 이스케이프 문자로 '||'를 사용한 것을 볼 수 있습니다. 

'|'를 대체하기 위해 그래서 우리는 올바른 결과를 얻습니다.

'_my' 매개변수에 대한 함수를 호출하고 이전 예와 동일한 논리를 사용하면 동일한 올바른 결과를 얻을 수 있습니다.

 

USE TestDB
GO

DECLARE @myUser NVARCHAR(50) = '_my' 

SELECT dbo.udfReplaceWildcards(@myUser) AS 'AfterAddingEscapeCharacter'

SELECT *
FROM myUser
WWHERE LoginName LIKE '%' + dbo.udfReplaceWildcards(@myUser) + '%' ESCAPE '|'

 

 

 

다른 와일드카드를 사용하여 T-SQL 함수 테스트

아래에서는 지정된 패턴의 다른 와일드카드를 사용하여 이 함수를 테스트하고 있습니다.

 

USE TestDB
GO

-- _
DECLARE @myUser NVARCHAR(50) = '_'

SELECT *
FROM myUser
WHERE LoginName LIKE '%' + dbo.udfReplaceWildcards(@myUser) + '%' ESCAPE '|'

-- %
SET @myUser  = '%'

SELECT *
FROM myUser
WHERE LoginName LIKE '%' + dbo.udfReplaceWildcards(@myUser) + '%' ESCAPE '|'

-- [
SET @myUser  = '['

SELECT *
FROM myUser
WHERE LoginName LIKE '%' + dbo.udfReplaceWildcards(@myUser) + '%' ESCAPE '|'

-- ]
SET @myUser = ']'

SELECT *
FROM myUser
WWHERE LoginName LIKE '%' + dbo.udfReplaceWildcards(@myUser) + '%' ESCAPE '|'

 

 

 

 

결론

LIKE 연산자는 SQL SERVER에서 자주 사용되지만 때때로 사용하기 까다로울 수 있습니다. 와일드카드 문자를 포함한 LIKE 패턴을 이해하면 이 연산자를 보다 자신 있게 사용할 수 있습니다.

 

 

 

원본글 및 출처

 

https://www.mssqltips.com/sqlservertip/5670/examples-and-function-for-using-sql-server-like-operator-and-wildcard-characters/

SQL Server LIKE Syntax with Wildcard Characters