clr

EXEC sp_configure 'clr enabled', 1
RECONFIGURE WITH OVERRIDE
GO

----创建创建包含类元数据和托管代码的托管应用程序模块，将其作为 SQL Server 实例中的对象。
CREATE ASSEMBLY [Goophee.SQLTools] FROM 'F:Work微桥商城SpiderWQSpiderSQLCLRinReleaseSQLCLR.dll'
GO

CREATE AGGREGATE JoinString (@input nvarchar(200),@seperator nvarchar(1)) RETURNS nvarchar(max) 
EXTERNAL NAME [Goophee.SQLTools].[SQLCLR.JoinString]
Go

CREATE FUNCTION dbo.RegLike 
(  
    @input  nvarchar(max ),
    @pattern  nvarchar(4000)
)    
RETURNS bit
AS EXTERNAL NAME [Goophee.SQLTools].[SQLCLR.RegExp].RegExp_Like
go

CREATE FUNCTION dbo.RegReplace 
(  
    @input  nvarchar(max ),
    @pattern  nvarchar(4000),
    @replacement nvarchar(4000)
)    
RETURNS   nvarchar(max )
AS EXTERNAL NAME [Goophee.SQLTools].[SQLCLR.RegExp].RegExp_Replace
go

drop AGGREGATE JoinString
drop FUNCTION RegLike
drop FUNCTION RegReplace
drop ASSEMBLY [Goophee.SQLTools]

;with cte as
(
    select top 10 id from blog
)
select dbo.JoinString(id,',') from cte 

select top 1 id from blog

select dbo.RegReplace(contents,'<[^>]*>','') from blog where id='DEF61B29-4817-43B3-9393-00000E0CADF2'