[MSSQL] 在 T-SQL 使用 PATINDEX 搜尋關鍵字

前言
今天接到個任務，需要在 MSSQL 資料表中找出特定規則的資料內容，看到搜尋格式直覺可以用 Regualr Expression 輕鬆解決，之前使用 Regualr Expression 都是在前端 Javascript 或是 C# 上使用，尚未在 T-SQL 中寫過，經過研究之後在 SQL 中可以使用 PATINDEX 方法可達到同樣目的且結果正確，以下就針對今天學到的內容做說明與介紹，若有問題歡迎提出一起討論或是給予指導

PATINDEX 介紹
一開始我們可以先來看一下 PATINDEX 方法 API 

PATINDEX ( '%pattern%' , expression ) 

 pattern  : 要搜尋的 key word，前後需要用 %%包起來 (第一個字元或最後一個不用加%)，限制 8000 字元
 expression  : 被搜尋的內容
 return  : int，搜尋內容的位置
相信光看沒有感覺，以下列出目前所想到的情境做介紹

Case 1 : 使用 %搜尋特定關鍵字
在 Hello world 字串中找尋 'lo' 關鍵字，並返回關鍵字位置 

Declare @input varchar(20) = 'Hello world!'  
SELECT PATINDEX('%lo%', @input) 
-- result : 4 

Case 2 : 使用 [] 搜尋特定關鍵字
在 A123456789 字串中找尋 英文開頭 ( 左邊沒加上%)，以及英文字母在中間及最右邊位置，都可正常找到並返回關鍵字位置  

Declare @input varchar(20) = 'A123456789'  
SELECT PATINDEX('A-z%', @input) 
-- result : 1

Declare @input2 varchar(20) = '123456789A'  
SELECT PATINDEX('%[A-z]', @input2) 
-- result : 10

Declare @input3 varchar(20) = '123456Q789'  
SELECT PATINDEX('%[A-z]%', @input3) 
-- result : 7

Case 3 : 在 Where 中過濾條件
使用北風資料庫的 customer 的 PostalCode 欄位，搜尋欄位內容為數字的資料

SELECT TOP 100 *
FROM [Northwind].[dbo].[Customers]
WHERE PATINDEX('[0-9]%', PostalCode) > 0

搜尋前

搜尋後


Case 4 : 搭配 Case When 使用
接著繼續使用北風資料庫的 customer 的 Phone 欄位，希望搜尋欄位為 (數字) 的電話，如果是的話顯示 O不是則顯示 X，如下所示

SELECT TOP 100 phone, 
  CASE WHEN PATINDEX('%([0-9])%', Phone) > 0
  THEN 'O' ELSE 'X' end AS 'Phone2'
FROM [Northwind].[dbo].[Customers]

搜尋結果

Performance
在 MSDN 有提到， PATINDEX  工作原理就像  LIKE ，因此我在上述範例四用 LIKE 語法改寫，再加上開啟 SSMS 執行計畫觀察效能情況

-- PATINDEX
SELECT TOP 1000 phone, 
  CASE WHEN PATINDEX('%([0-9])%', Phone) > 0
  THEN 'O' ELSE 'X' end AS 'PhoneFormat', *
FROM [Northwind].[dbo].[Customers]

-- LIKE
SELECT TOP 1000 phone, 
  CASE WHEN Phone LIKE '(%' 
  THEN 'O' ELSE 'X' end AS 'PhoneFormat'
FROM [Northwind].[dbo].[Customers]

抓出來的資料相同且從執行計畫看起來兩者分別 query cost 各為 50%，就初步觀察並無太大差異，或許之後可以最更進一步的測試與研究來看 performance 的影響

感想
在 MSSQL 中提供很多功能可以達到想要的目的，但其實在使用的背後犧牲的或許是 CPU 或是 Memory 的成本，因此在思考時或許可以看是否可以在前端或是 API 時就進行檢查的動作，或是再使用前更加了解語法對 performance 的影響，讓 Database 可以發揮更大的作用及正確的使用方式 :)

參考
PATINDEX（Transact-SQL）