CLR integration in sqlserver

CLR integration in sqlserver

http://www.codeproject.com/Articles/19502/A-T-SQL-Regular-Expression-Library-for-SQL-Server:

Step 1

--Enable CLR Integration
exec sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO


Step 2:

CREATE ASSEMBLY [SqlRegEx] FROM 'C:\Project\SqlRegEx.dll' WITH PERMISSION_SET = SAFE

Step 3:

CREATE FUNCTION [dbo].[ufn_RegExIsMatch]
(@Input NVARCHAR(MAX), @Pattern NVARCHAR(MAX), @IgnoreCase BIT)
RETURNS BIT
AS EXTERNAL NAME SqlRegEx.[SqlClrTools.SqlRegEx].RegExIsMatch

Note:


SELECT dbo.ufn_RegExIsMatch('Hello World', 'w', 1) --Ignores Case
SELECT dbo.ufn_RegExIsMatch('Hello World', 'w', 0) --Case Sensitive


Example:
Find if Extended Acsii value
select * from TableName where dbo.ufn_RegExIsMatch(ColumnName,'[^\u0000-\u007F]',0 )=1