Blog 中的每一篇文章,都可以設定多個關鍵字,常見型式為利用逗號(,)來串接每個關鍵字,EX:SQL,T-SQL,CTE,Split,此型式資料不容易拿來利用,因此需要把它正規化。
CHARINDEX() 函數說明
CHARINDEX ( expressionToFind ,expressionToSearch [ , start_location ] )語法
- expressionToFind 代表要在字串中尋找的目標(逗號)
- expressionToSearch 代表被搜尋字串(關鍵字)
- start_location 未指定、負數或 0,搜尋就會從 expressionToSearch 開頭開始,設定大於 0 的整數,代表這個字串位置開始往後搜尋
CTE 解法
DECLARE @Temp TABLE (PostTitle char(100),Keyword char(100))
INSERT INTO @Temp VALUES('[SQL] 模擬死結產生','SQL,DealLock')
INSERT INTO @Temp VALUES('[Windows] Win7 和 內碼輸入法','Windows 7,內碼,輸入法')
INSERT INTO @Temp VALUES('阿朗壹古道','CYMC,Hiking')
INSERT INTO @Temp VALUES('威力導演 9 - 直式相片模糊','威力導演,模糊,Video')
INSERT INTO @Temp VALUES('馬桶漏水','居家生活,馬桶,乾式安裝,漏水,HCG,和成')
;
WITH CTE AS
(
SELECT
PostTitle ,
Keyword ,
1 AS StartPos , -- 錨點起點
CHARINDEX(',',Keyword) AS EndPos -- 錨點終點
FROM @Temp
UNION ALL
SELECT
PostTitle ,
Keyword ,
EndPos + 1 , -- 遞迴起點
CHARINDEX(',',Keyword,EndPos + 1) -- 遞迴終點
FROM CTE
WHERE EndPos > 0 -- CHARINDEX() 大於 0 表示還有逗號存在,遞迴繼續跑,等於 0 則遞迴停止
)
SELECT
PostTitle ,
SUBSTRING
(
Keyword ,
StartPos ,
ISNULL -- 第一個參數假如為 NULL 就抓取第二參數來表示終點
(
NULLIF(EndPos,0) , -- 第一參數;利用 NULLIF() 把終點為 0 的值轉換成 NULL
LEN(Keyword) + 1 -- 第二參數
)
- StartPos
) AS String
FROM CTE
ORDER BY
PostTitle ,
StartPos -- 對起點進行排序,能確保關鍵字呈現順序
CTE 解法說明
CTE 錨點內設定 1 為錨點起點,關鍵字最左邊逗號為錨點終點,遞迴部分則是把終點 + 1 當成遞迴起點,利用指定 CHARINDEX() 第三個參數,繼續往後尋找最近逗號,當遞迴終點大於 0(表示 CHARINDEX() 有找到逗號),遞迴就必須繼續,等於 0 的話,遞迴就會停止
此圖為 CTE 跑完後每篇文章的每個關鍵字起始和結束位置一覽表。
正常情況下,CTE 跑出的起始和結束位置就可以找出關鍵字,但因 CTE 是用 CHARINDEX() 是否找到逗號來當成停止條件,因此每篇文章最後的關鍵字終點必為 0。
利用 NULLIF() 來尋找終點為 0 的資料,把它取代為 NULL ,再利用 ISNULL() 來判斷,不是 NULL 就直接抓取終點,NULL 則是找關鍵字總長度來當成終點,終點經過轉換後,就可以利用 SUBSTRING() 來抓取每個關鍵字。