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
-------------------------------------------------------------------------------------------------------
