前言
今天接到個任務，需要在 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）
