[SQL]取得欄位中的數字,其他中英文符號一律略過

這是同事問的問題,將它記錄下來

需求是:若欄位裡的值是中英文及數字夾雜,該如何取得數字的部份就好

找到一個解決方法,原文網址如下

http://justgeeks.blogspot.tw/2008/08/adding-regular-expressions-regex-to-sql.html

簡單來說就是將程式寫成 assembly 匯入SQL ,透過 User Function 呼叫,
我將它整理出來

首先,先建立ClassLibrary專案,專案名稱叫: RegexMatch

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Text.RegularExpressions;
using System.Text;

public partial class UserDefinedFunctions
{
    public static readonly RegexOptions Options = RegexOptions.IgnorePatternWhitespace | RegexOptions.Multiline;
    [SqlFunction]
    public static SqlBoolean RegexMatch(SqlChars input, SqlString pattern)
    {
        Regex regex = new Regex(pattern.Value, Options);
        return regex.IsMatch(new string(input.Value));
    }
    [SqlFunction]
    public static SqlString RegexReplace(SqlString expression, SqlString pattern, SqlString replace)
    {
        if (expression.IsNull || pattern.IsNull || replace.IsNull)
            return SqlString.Null;
        Regex r = new Regex(pattern.ToString());
        return new SqlString(r.Replace(expression.ToString(), replace.ToString()));
    }
    // returns the matching string. Results are separated by 3rd parameter
    [SqlFunction]
    public static SqlString RegexSelectAll(SqlChars input, SqlString pattern, SqlString matchDelimiter)
    {
        Regex regex = new Regex(pattern.Value, Options);
        Match results = regex.Match(new string(input.Value));
        StringBuilder sb = new StringBuilder();
        while (results.Success)
        {
            sb.Append(results.Value);
            results = results.NextMatch();
            // separate the results with newline|newline
            if (results.Success)
            {
                sb.Append(matchDelimiter.Value);
            }
        }
        return new SqlString(sb.ToString());
    }
    // returns the matching string
    // matchIndex is the zero-based index of the results. 0 for the 1st match, 1, for 2nd match, etc
    [SqlFunction]
    public static SqlString RegexSelectOne(SqlChars input, SqlString pattern, SqlInt32 matchIndex)
    {
        Regex regex = new Regex(pattern.Value, Options);
        Match results = regex.Match(new string(input.Value));
        string resultStr = "";
        int index = 0;
        while (results.Success)
        {
            if (index == matchIndex)
            {
                resultStr = results.Value.ToString();
            }
            results = results.NextMatch();
            index++;
        }
        return new SqlString(resultStr);
    }
};

專案build 後,將 RegexMatch.dll (在專案的bin目錄底下) 複製到要建立組件的SQL Server的那台電腦目錄下,例如放在 D:\ 


接下來就在SQL 建立組件(assembly) ,新增 function




USE yourDataBase;
GO


EXEC sp_configure 'clr enabled', '1'
GO


CREATE ASSEMBLY asmRexExp
FROM  'D:\RegexMatch.dll'
WITH PERMISSION_SET = SAFE;




CREATE FUNCTION [dbo].[RegexMatch](@input [nvarchar](max), @pattern
[nvarchar](max))
RETURNS [bit] WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [asmRexExp].[UserDefinedFunctions].[RegexMatch]
GO




CREATE FUNCTION [dbo].[RegexReplace](@expression [nvarchar](max), @pattern
[nvarchar](max), @replace [nvarchar](max))
RETURNS [nvarchar](max) WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [asmRexExp].[UserDefinedFunctions].[RegexReplace]
GO

CREATE FUNCTION [dbo].[RegexSelectAll](@input [nvarchar](max), @pattern
[nvarchar](max), @matchDelimiter [nvarchar](max))
RETURNS [nvarchar](max) WITH EXECUTE AS CALLER
AS
EXTERNAL NAME
[asmRexExp].[UserDefinedFunctions].[RegexSelectAll]
GO


CREATE FUNCTION [dbo].[RegexSelectOne](@input [nvarchar](max), @pattern
[nvarchar](max), @matchIndex [int])
RETURNS [nvarchar](max) WITH EXECUTE AS CALLER
AS
EXTERNAL NAME
[asmRexExp].[UserDefinedFunctions].[RegexSelectOne]
GO

這樣就好啦,以上四個 function 都開始使用了
使用方式就是


select dbo.RegexSelectAll(要查詢的欄位,RegexPettern,分隔字符)
select dbo.RegexSelectAll('123-- @abc*中英文字323','\d','')


結果會是 
123323