星期五, 6月 15, 2012

[SQL] 特定符號隔離字串資料

Blog 中的每一篇文章,都可以設定多個關鍵字,常見型式為利用逗號(,)來串接每個關鍵字,EX:SQL,T-SQL,CTE,Split,此型式資料不容易拿來利用,因此需要把它正規化。

CHARINDEX() 函數說明

CHARINDEX ( expressionToFind ,expressionToSearch [ , start_location ] )語法
  1. expressionToFind 代表要在字串中尋找的目標(逗號)
  2. expressionToSearch 代表被搜尋字串(關鍵字)
  3. 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() 來抓取每個關鍵字。

2 則留言:

ohfish 提到...

謝謝分享

TerryTsai 提到...

SQL Server 2016 可以使用 STRING_SPLIT() 函數來處理,效能上會比較好喔

張貼留言