[MSSQL] 在 T-SQL 使用 PATINDEX 搜尋關鍵字 ~ m@rcus 學習筆記

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

[SQL] 使用 Universal Data Link (.udl) 快速測試資料庫連線問題這幾天同事開發的應用程式佈署到 Production 機器時，發現原本在測試機器可以運行的功能到正式環境的機器後就無法正常使用，懷疑部署到的機器是否無法正常連到正式環境 Database，這篇就來分享遇到此問題時該如何進行確認 Database 連線字串的動作，也感謝之前主管傳授此密技來快速測試連線字串，這邊若有謬誤或是更快的方法歡迎留言一起討論。解決方案一般第一個想到的方式可以開啟命令提示字元，… Read More
[MSSQL] 在 T-SQL 使用 PATINDEX 搜尋關鍵字前言今天接到個任務，需要在 MSSQL 資料表中找出特定規則的資料內容，看到搜尋格式直覺可以用 Regualr Expression 輕鬆解決，之前使用 Regualr Expression 都是在前端 Javascript 或是 C# 上使用，尚未在 T-SQL 中寫過，經過研究之後在 SQL 中可以使用 PATINDEX 方法可達到同樣目的且結果正確，以下就針對今天學到的內容做說明與介紹，若有問題歡迎提出一起討論或是給予… Read More
[NET] Execution of user code in the .NET Framework is disabled. Enable "clr enabled" configuration option.問題最近運氣很旺在開發專案時常遇到詭異的案例，前幾天在家裡趕專案時在使用 Visual Studio 2019 開發專案要啟動連接到 Database 時跳出 'Execution of user code in the .NET Framework is disabled. Enable "clr enabled" configuration option.' 第一次遇到這問題覺得挺有趣的，雖然解決… Read More
[sharing] MS T-SQL 效能調教從放棄到入門分享自己在過去開發經驗都是以 C# 與 ASP.NET 為主，在與資料庫溝通時都是自己寫 T-SQL 查詢語法使用 Stored procedure 讀取需要的資料，身為工程師就必須特別留意自己撰寫語法的效能問題，盡量避免寫出來的語法效能不佳影響到 Database 或是網站的運行，自己過去工作經驗也遇到公司使用很頂級的伺服器依舊存在效能不彰的問題，因此效能調教部分個人覺得也是資深工程師的必備的技能之一。下面是前一份工作與同事分享的投影片，主… Read More
[CheatSheets] SQL JOIN Cheat Sheets 前言最近在 facebook 有善心人士分享 SQL 各種 JOIN 筆記與差異，看完覺得相當實用特記錄在部落格中，做為日後小抄使用 :) 參考 Sam Chuang … Read More

m@rcus 學習筆記

只有累積，沒有奇蹟

2019年3月15日星期五