sql server 导入c#dll

1.编写c# 类库

        [SqlFunction(IsDeterministic = true, DataAccess = DataAccessKind.None)]
        public static SqlString GetKeyWords(SqlString input, SqlString pattern)
        {
     
                
              GroupCollection groups = Regex.Match(input.ToString(), pattern.ToString()).Groups;

            return (SqlString)groups[1].Value;
        }

2.导入dll

exec sp_configure 'clr enabled', 1;
reconfigure;

use JRE;
create assembly RegExp from'C:\Users\Administrator\Documents\visual studio 2015\Projects\Jsonparser\Jsonparser\bin\Debug\Jsonparser.dll'

3.编写对应的函数

CREATE FUNCTION dbo.GetKeyWords
    (
      @input AS NVARCHAR(MAX) ,
      @pattern AS NVARCHAR(MAX)
    )
RETURNS NVARCHAR(MAX)
    WITH RETURNS NULL ON NULL INPUT
EXTERNAL NAME
    [RegExp].[Jsonparser.RegExp].[GetKeyWords];

4.调用函数

SELECT dbo.GetKeyWords('http://webapi.jrechina.com/MobileRequest.ashx?action=Jre&lan=1&Method=T_Houses.JreWebEnSearch&Cont={"ID":"137331"%2C"Category":"Near"}&EType=Soa&page=1&rows=8','"ID":"(.*)"%2C|\}')

5.常用正则

"ID":"(\d{1,10})"
"KeyWord":"(.*)"