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:UsersAdministratorDocumentsvisual studio 2015ProjectsJsonparserJsonparserinDebugJsonparser.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":"(.*)"