~楓花雪岳~: [SQL] 特定符號隔離字串資料

星期五, 6月 15, 2012

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

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() 來抓取每個關鍵字。

結果