SQL來查詢特定字元出現的次數

前言:

本次要來說說如何用SQL來查詢特定字元出現的次數。

為何會有這種需求呢(特別是製造業常用的ERP)? 主因是很多時候ERP的功能無法與時俱進或是因為成本考量，而缺少部分功能，但又需要記錄一些資料作為篩選之類的用途，因此會把一些篩選條件等文字存入用於存放敘述性的欄位當中。但倘若只是用於篩選，其實針對特定欄位撰寫 LIKE ‘%想要搜尋的字串%’也就把問題解決了。

那為何要數字串出現的數目?

以作者親身的經驗，是因為有些耗材的領用量難以估計，也不會列在BOM表，但那種類的耗材相當昂貴，所以該公司還是需要知道預估的耗用數量，而此耗材品名有記錄於規格當中，當規格撰寫越多次此耗材，代表我們可以預期會使用到的耗材數量越多。

所以接下來作者就會以自己的思路與大家討論可能的作法，以及網路上看到的不錯做法。

內文:

如何找出特定字串?

如果今天不考量字串被找到的次數，而我們只要找到或是知道字串出現在哪，其實在MSSQL(SQL Server 2008以後的版本)裡面有兩個函數可以使用，一個是CHARINDEX，另一個是PATINDEX。以下參考MSDN上的Transact-SQL的語法:

CHARINDEX ( expressionToFind , expressionToSearch [ , start_location ] )  

PATINDEX ( '%pattern%' , expression ) 

所以對於大多數讀者來說，上面兩行語法非常清楚，CHARINEX就是將要尋找的字元(Char)放到參數1，而要被找搜尋的字元到參數2，而參數3則是選擇性的可用可不用，不使用則會預設為0，從被搜尋的字元第一個位置開始搜尋，反之，就會從使用者指定的位置開始找尋，該函數的回傳值則是第一次出現的起始位置。而PATINDEX顧名思義，就是用來找尋特定模式出現的位置，因此我們可以使用萬用字元，來描述字元的模式，若有符合則會回傳第一次出現的起始位置。

這兩個函數在SQL Server我常拿來用於切字串取得所需要的資料，因為資料庫有時候還是有描述性的資料，需要去蕪存菁一下。

如何找出特定字串出現的次數?

若比較直覺的想法，是以迴圈進行處理，配合上述介紹的兩個函數，若回傳值不為0，則代表出現次數+1，並從該回傳Index位置+查詢字元的長度之後繼續查詢。但網路上其實有提供一個特別的方法，巧妙的運用replace來達成，replace同樣也能達到查詢指定字元，並將將該指定字元以指定的字元取代。筆者通常會用replace來取代NULL值，來讓資料維持一致性。他的語法在T-SQL的網站上如下式:

REPLACE ( string_expression , string_pattern , string_replacement )

網路上有Oracle版本的文章，因此本文修改成以MSSQL-Server2008為例形式，假設我們有一個字串123x456x123x456，而我們要找尋x出現的次數，則我們以replace將查詢到的x以空白取代，則字串會縮短，而此縮短的長度總和除以單一一個要查詢的字元長度，就會是他被查詢到的次數。

範例Code:

執行結果:

參考資料:

SQL查詢特定字串出現的次數 http://hanjay.pixnet.net/blog/post/30025506-sql%E6%9F%A5%E8%A9%A2%E7%89%B9%E5%AE%9A%E5%AD%97%E4%B8%B2%E5%87%BA%E7%8F%BE%E7%9A%84%E6%AC%A1%E6%95%B8