사용자 함수를 만드는 것은 이전에도 가능했지만, CLR의 도입으로 그 일이 훨씬 편해졌다.
'Inside Microsoft SQL Server 2005: T-SQL Programming'에 나온 예제로서, CLR 쪽의 정규식 함수를 SQL Server 쪽에서 사용하는 예제다. .NET 버전 차이 때문에 에러나는 부분들은 수정했다. (2.0 기준)
using System; using System.Collections.Generic; using System.Collections; using System.Data; using System.Data.SqlClient; using System.Data.SqlTypes; //using System.Diagnostics; //using System.Reflection; using System.Text; using System.Text.RegularExpressions; using Microsoft.SqlServer.Server; namespace ClrUtility { public partial class ClrUtility { [SqlFunction(IsDeterministic = true, DataAccess = DataAccessKind.None)] public static SqlBoolean fn_RegExMatch(SqlString inpStr, SqlString regExStr) { if (inpStr.IsNull || regExStr.IsNull) return SqlBoolean.Null; else return (SqlBoolean)Regex.IsMatch(inpStr.Value, regExStr.Value, RegexOptions.CultureInvariant); } } }
컴파일하고 DLL을 어딘가에 둔다. 어트리뷰트 쪽 좀 조사해봐야할 것 같은데.
SET NOCOUNT ON; USE master; EXEC sp_configure 'clr enabled', 1; RECONFIGURE; GO IF DB_ID('ClrUtilityDatabase') IS NOT NULL DROP DATABASE ClrUtilityDatabase; GO CREATE DATABASE ClrUtilityDatabase; GO USE ClrUtilityDatabase; GO CREATE ASSEMBLY ClrUtility FROM 'C:\ClrUtility.dll' WITH PERMISSION_SET = SAFE;
PERMISSION_SET 부분에 들어가는 내용은 다음과 같다.
참고로 EXTERNAL_ACCESS 이상의 권한을 주기 위해서는 아래와 같은 명령어를 통해 TRUSTWORTHY 속성을 켜줘야 한다.
#!SQL ALTER DATABASE ClrUtilityDatabase SET TRUSTWORTHY ON
USE ClrUtilityDatabase; GO IF OBJECT_ID('dbo.fn_RegExMatch') IS NOT NULL DROP FUNCTION dbo.fn_RegExMatch; GO CREATE FUNCTION dbo.fn_RegExMatch (@inpStr AS NVARCHAR(MAX), @regExStr AS NVARCHAR(MAX)) RETURNS BIT EXTERNAL NAME ClrUtility.[ClrUtility.ClrUtility].fn_RegExMatch;
네임스페이스 부분 개같으니 주의.
USE ClrUtilityDatabase; SELECT dbo.fn_RegExMatch('abcd', '[a-z]+');