去除特殊符號與中、英數分離
為了的到更乾淨純粹的資料，常常需要從雜亂的資料中取出重要的部分。如果文章都是以中文為主的話，就可以考慮斷開中文、英數與其他字符一共 3 個種類。
在 SQL Server 中很難到這一點，因為它不支援 Regular Expression(RegExp) 這類的功能。因此我這邊利用中英數 Unicode 範圍的策略來實現。
判斷是否為英數
在 SQL Server 中提供了一些「簡單的」Pattern Matching 的機制，提供我們做一些簡單的比對功能，以下是一個判斷是否為英數的片段：
if 'A' like '%[a-Z0-9]%' print 'YES' else print 'NO'
雖然 Pattern Matching 很接近 RegExp，但僅能比對官方文件中所述的幾種 Pattern，並無法像真正的 RegExp 一樣靈活。
判斷是否為中文
由於無法使用 RegExp，這邊使用 Unicode 來判斷
select unicode('嗨') -- output 21992
根據 Unicode 官方描述，漢字集中收錄在 CJK Unified Ideographs (CJK統一漢字) 中，其中收錄了中日韓三種主要常見的文字，細節可以參閱官方文件。
其中，所有的漢字範圍應在 4E00 - 9FD5 之間，在這個範圍中的文字就規成一類。
-- 4E00(hex) = 19968(decimal) -- 9FD5(hex) = 40917(decimal) declare @i int = 19968 while @i<=40917 begin print cast(@i as nvarchar(6))+' '+nchar(@i) set @i+=1 end
output
19968 一 19969 丁 19970 丂 19971 七 19972 丄 19973 丅 19974 丆 19975 万 19976 丈 19977 三 19978 上 19979 下 ...
上面使用 NCHAR 來將 Unicode 轉為顯示的字元，實際使用上不需要。
不要枚舉黑名單，譬如 Delimiter，因為一定會有漏掉，正確做法應該是用白名單機制，這樣取出的資料才有源頭的規則可循。
分離
透過上述兩個方法，撰寫一個簡單的迴圈，掃過字串中每一個字元，並將其分開。
declare @str nvarchar(500)= N'★歡慶10周年★賀!!Happy New Year~ 全部 70% OFF喔' declare @len int = len(@str) declare @i int = 1 declare @enPattern nvarchar(15) = '%[-a-Z0-9_'']%' declare @tmpStr nvarchar(100)='' declare @tmpChar nvarchar(2) declare @prevmode int = null declare @mode int = null while(@i<=@len) begin set @tmpChar=substring(@str,@i,1) set @i+=1 -- mode:1 英數 if @tmpChar like @enPattern set @mode=1 -- mode:2 漢字 else if unicode(@tmpChar) between 19968 and 40917 set @mode=2 -- mode:0 其他 else set @mode=0 -- 跟上一個字一樣 mode if @prevmode = @mode set @tmpStr+=@tmpChar else -- 跟上一個字不同 mode begin -- 暫存的是 mode 1 or 2 才是要的 if @prevmode in(1,2) print @tmpStr set @tmpStr = @tmpChar set @prevmode = @mode end end -- 暫存裡面清出 if len(@tmpStr)>0 if @prevmode in(1,2) print @tmpStr
output
「★歡慶10周年★賀!!Happy New Year~ 全部 70% OFF喔」 會被處理成以下這樣：
歡慶 10 周年 賀 Happy New Year 全部 70 OFF 喔
在上面這個範例中，你可以自行增加 mode 來處理更多種類的文字
Table-Valued User-Defined Functions
透過 Table-Valued User-Defined Functions 我們可以將上面的功能包裝成 Segmentation Function 以便我們後續輕鬆呼叫。
create function segmentation(@text nvarchar(4000)) returns @segments table ( segment nvarchar(4000) NULL ) as begin declare @len int = len(@text) declare @i int = 1 declare @enPattern nvarchar(15) = '%[-a-Z0-9_'']%' declare @tmpStr nvarchar(4000)='' declare @tmpChar nvarchar(2) declare @prevmode int = null declare @mode int = null while(@i<=@len) begin set @tmpChar=substring(@text,@i,1) set @i+=1 -- mode:1 英數 if @tmpChar like @enPattern set @mode=1 -- mode:2 漢字 else if unicode(@tmpChar) between 19968 and 40917 set @mode=2 -- mode:0 其他 else set @mode=0 -- 跟上一個字一樣 mode if @prevmode = @mode set @tmpStr+=@tmpChar else -- 跟上一個字不同 mode begin -- 暫存的是 mode 1 or 2 才是要的 if @prevmode in(1,2) insert @segments values(@tmpStr) set @tmpStr = @tmpChar set @prevmode = @mode end end -- 暫存裡面清出 if len(@tmpStr)>0 if @prevmode in(1,2) insert @segments values(@tmpStr) return; end
Call Function
select * from segmentation(N'★歡慶10周年★賀!!Happy New Year~ 全部 70% OFF喔')
Regular Expression
SQL Server 不支持 Regular Expression 實在非常不方便，但還好它內建整合的 Common Language Runtime (CLR) 可以幫助我們做到這一點。CLR 是一個能夠執行 .NET Framework 程式碼的環境，所以我們可以藉由撰寫 Managed 程式碼(CLR程式碼)，來擴充 SQL Server 不足之處。
啟用 CLR
sp_configure 'clr enabled', 1 GO RECONFIGURE GO
建立 CLR 專案
為了讓 SQL Server 也能呼叫 RegExp 的功能，我將借用 C# 內建的 Regex 來完成。
開啟專案
開啟 SQL Server 範本中的 SQL Server Database Project
加入 SQL CLR User Defined Function
在加入新項目 (Ctrl+Shift+A) 中，選擇加入 SQL CLR C# 範本中的 SQL CLR User Defined Function，並修改 FileName (e.g. PatternMatching.cs or RegExp.cs ... )
CLR Table-Valued Functions
在這個 Case 中，我希望做完 RegExp 的資料能以 Table 的形式回傳，這邊使用 SQL Server 中「資料表值使用者定義函數」來實現。根據官方文件說明，實作 CLR Table-Valued User Defined Function 必須實做 IEnumerable 介面，並且指定 FillRow 方法，將程式碼改成以下這樣：
public class PatternMatching { [SqlFunction(FillRowMethodName = "FillRow")] public static IEnumerable RegExp() { return null; } public static void FillRow(Object obj, out SqlChars col1) { col1 = null; } }
其中，
實做 IEnumerable 介面
接下來就只剩下 RegExp 的功能了，由於 RegExp 並不是一個以
using System; using System.Collections; public class IRegExp : IEnumerable { public IEnumerator GetEnumerator() { throw new NotImplementedException(); } }
class RegExpEnum : IEnumerator { public object Current { get { throw new NotImplementedException(); } } public bool MoveNext() { throw new NotImplementedException(); } public void Reset() { throw new NotImplementedException(); } }
IEnumerable 的詳細介紹可以參閱官方文件，其中有詳盡的說明。
加入 RegExp
有了 IEnumerable 與 IEnumerator 後，就可以將 RegExp 程式碼補上去，像是這樣：
// 執行 RegExp 功能 public class IRegExp : IEnumerable { private List<string> segments; // 參數 // @pattern (RegExp) // @text 要處理的文字 public IRegExp(string pattern, string text) { // 進行 RegExp Regex r = new Regex(pattern); Match m = r.Match(text); if (m.Success) segments = new List<string>(); while (m.Success) { foreach (Group g in m.Groups) { foreach (Capture c in g.Captures) { // 將結果儲存起來 segments.Add(c.Value); } } m = m.NextMatch(); } } public IEnumerator GetEnumerator() { // 將結果傳入並回傳自訂的迭代控制器 return new RegExpEnum(segments.ToArray()); } } // 迭代控制器 class RegExpEnum : IEnumerator { private string[] _segment; private int pos = -1; // 將資料傳入 public RegExpEnum(string[] segment) { _segment = segment; } public string Current { get { try { return _segment[pos]; } catch (IndexOutOfRangeException) { throw new InvalidOperationException(); } } } object IEnumerator.Current { get { return Current; } } public bool MoveNext() { pos++; return (pos < _segment.Length); } public void Reset() { pos = -1; } }
讓 CLR Table-Valued User Defined Function 使用
最後，在 Managed 程式碼中將自訂的 IEnumerable 物件回傳，並實作 FillRow 方法就大功告成了：
public class PatternMatching { [SqlFunction(FillRowMethodName = "FillRow")] public static IEnumerable RegExp(string pattern,string text) { return (IEnumerable)new IRegExp(pattern, text); } public static void FillRow(object row,out SqlChars segment) { segment = new SqlChars(row.ToString()); } }
其中，因為在 RegExp 方法中加上
從 SQL Server 中加入組件
接下來，將專案建置後，就會產生一個編譯好的
Create ASSEMBLY RegExp FROM 'D:\...\obj\Debug\PatternMatching.dll' WITH PERMISSION_SET = EXTERNAL_ACCESS
這裡的Create ASSEMBLY [組件名稱] 你可以自己決定！
建立 Table-Valued User Defined Function
現在，在 SQL Server 中已經存在
透過建立 Table-Valued Function 來呼叫它，像是以下這樣：
CREATE FUNCTION RegExp ( @pattern nvarchar(200), @text nvarchar(4000) ) RETURNS TABLE ( pattern nvarchar(4000) ) AS EXTERNAL NAME RegExp.PatternMatching.RegExp; GO
呼叫外部組件的語法規則：EXTERNAL NAME [組件名稱].[Class名稱].[方法名稱] 如果你的名稱跟我的不同，可以自行修改！
從 SQL Server 中呼叫 RegExp
現在我們可以從 SQL Server 中呼叫 RegExp 了：
select * from dbo.RegExp('(\w)3','A5B3C3D2E6F7G3H2I3')
結論
在上面的介紹中簡單介紹了使用 Unicode 來處理中文，並做了漢字與英數分開的功能。下面簡單介紹了一個整合 SQL CLR 的實際案例，透過 C# 來補足 SQL Server 不足之處。也可以將它們整合就能做出更符合你需求的 Pattern Extraction 或是 Segmentation 的功能。
在 SQL Server 中運算這些資料，並不是唯一的做法，依該視情況而定。
沒有留言:
張貼留言