SQL Server 다양한 와일드카드와 LIKE 연산자의 쓰임새

/* 다양한 와일드카드와 LIKE 연산자의 쓰임새*/

 

use AdventureWorks2014

go

 

sp_helpindex [HumanResources.Department]

/*

AK_Department_Name    nonclustered, unique located on PRIMARY    Name

PK_Department_DepartmentID    clustered, unique, primary key located on PRIMARY    DepartmentID

*/

 

 

-- p 로 시작하는 문자열 검색

SELECT name from HumanResources.Department WHERE Name LIKE 'p%'; -- index seek

SELECT name from HumanResources.Department WHERE Name LIKE '%p%'; -- index scan

 

 

 

 

-- 언더스코어(-)는 앞뒤 문자열로 검색하여 가져오기 여러개도 가능하다.

SELECT name from HumanResources.Department WHERE Name LIKE 'pro%';

SELECT name from HumanResources.Department WHERE Name LIKE 'p_o%';

 

 

 

 

SELECT * from HumanResources.Department WHERE Name LIKE 'prod%';

SELECT * from HumanResources.Department WHERE Name LIKE 'p__d%';

 

 

-- E, M, P 로 시작하는 문자열 검색 Square brackets([])

SELECT * from HumanResources.Department WHERE Name LIKE '[emp]%';

 

 

 

-- m ~ q 문자열 모두 검색

SELECT * from HumanResources.Department WHERE Name LIKE '[m-q]%';

 

 

 

-- ^ = Not 과 같다, 인덱스는 스캔한다.

SELECT name from HumanResources.Department WHERE Name LIKE '[^m-q]%';

SELECT name from HumanResources.Department WHERE Name NOT LIKE '[m-q]%';

 

[참고문서]

http://www.sqlservercurry.com/2015/09/sql-server-using-like-with-wildcards.html