SQL 字串中 計算/統計 特定字串出現次數

資料來源: http://jengting.blogspot.com/2012/09/string-occur-times.html

Code

SELECT 
  T.String ,
  (LEN(T.String) - LEN(REPLACE(T.String,'A',''))) / LEN('A') AS 'A 出現次數',
  (LEN(T.String) - LEN(REPLACE(T.String,'23',''))) / LEN('23')  AS '23 出現次數' ,
  (LEN(T.String) - LEN(REPLACE(T.String,'DEF',''))) / LEN('DEF')  AS 'DEF 出現次數'
FROM
  (
    SELECT '123456123456' AS String
    UNION ALL
    SELECT 'ABCDEF'
    UNION ALL
    SELECT '123ABC321'
    UNION ALL
    SELECT 'DEF456ABC'
  ) AS T

邏輯說明

    利用 REPLACE() 函數來取代特定字串，再利用原字串和特定字串取代後長度相減，除以特定字串長度來計算出現次數，下圖示把文字說明改為數學式來表達。