- CHARINDEX() 函數說明:
- 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 跑完後每篇文章的每個關鍵字起始和結束位置一覽表。
正常情況下,CTE 跑出的起始和結束位置就可以找出關鍵字,但因 CTE 是用 CHARINDEX() 是否找到逗號來當成停止條件,因此每篇文章最後的關鍵字終點必為 0。
利用 NULLIF() 來尋找終點為 0 的資料,把它取代為 NULL ,再利用 ISNULL() 來判斷,不是 NULL 就直接抓取終點,NULL 則是找關鍵字總長度來當成終點,終點經過轉換後,就可以利用 SUBSTRING() 來抓取每個關鍵字。
- 結果
- 參考資料
- Sql Server : 一欄位轉成多筆資料列 - 利用 Cross Apply
- [SQL SERVER]String Splitting效能比較
2 則留言:
謝謝分享
SQL Server 2016 可以使用 STRING_SPLIT() 函數來處理,效能上會比較好喔
張貼留言