MS SQL 에서 정규식을 사용할 일이 있어서 구글링 해 본 결과 기본적으로 제공되는 것은 없고
Visual Studio 에서 CLR 프로젝트를 생성하여 라이브러리로 빌드하면 함수로 사용할 수 있는 방법이 있다는 것을 알았다.
Regular Expressions In MS SQL Server using CLR
Regular Expressions in MS SQL Server 2005/2008
가장 먼저 선수되어야 할 것은 CLR 프로젝트 생성 이다.
Visual Studio C# 2010 Express 에서 코드 프로젝트의 소스를 열고 위의 두 링크에서 공개한 소스를 적당히 섞어서 클래스를 완성했다. 최종 메소드는 RegExMatch, RegExReplace, RegExMatches 세 개.
Release 모드로 빌드 후 SQL 서버의 적당한 위치에 빌드한 라이브러리 파일을 두고 MSSQL 에 어셈블리를 생성 (해당 라이브러리를 사용하겠다는 선언 같은 건가 보다) 한다.
CREATE ASSEMBLY 어셈블리명 FROM '라이브러리 파일 경로'
GO
처음부터 에러가 난다.
어셈블리 'XXXXX' 검사가 실패하여 어셈블리 'XXXXX'에 대한 CREATE ASSEMBLY가 실패했습니다.
참조된 어셈블리가 최신인지, 그리고 데이터베이스에서 실행할 수 있도록 외부 액세스에 대해 트러스트되어 있는지 또는 안전하지 않은지 확인하십시오.
해당 에러에 대한 내용을 찾아보니 딱 한줄의 답변. "닷넷 버전을 확인해보십시오."
아마도 빌드한 라이브러리의 닷넷 버전이 MSSQL 의 버전과 맞지 않아 생기는 오류인가 보다. Visual Studio 에서 프로젝트 속성을 확인해보니 4.0 버전으로 타겟팅 되어 있다. 구글링 중에 MSSQL 2008 은 3.5 버전으로 해야 한다고 했던 글이 얼핏 생각나서 변경 후 재 빌드. 다시 어셈블리를 생성하니 '오오- 된다'
위 과정에서 이런저런 DB 속성을 변경한 것이 있어서 첨부한다.
EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'clr enabled', 1
GO
RECONFIGURE
GOALTER DATABASE DB명 SET TRUSTWORTHY ON
GO
ALTER DATABASE DB명 SET COMPATIBILITY_LEVEL = 100
GO
show advanced options 은 뭐 별거 아닌거 같고, clr enabled 는 이게 enable 되지 않으면 아마도 CLR 프로젝트로 생성한 라이브러리를 사용할 수 없다는 것 이겠지. TRUSTWORTHY 는 아마도 보안과 관련된 옵션일 텐데 검색을 좀 해보니 각 어셈블리 별로 SAFE(디폴트), EXTERNAL_ACCESS, UNSAFE 와 같은 권한을 등록할 수 있는데 그것에 대한 체크 여부를 지정하는 것 같다. 이 부분에 대해서는 좀더 자료를 찾아봐야겠다.
COMPATIBILITY_LEVEL 은 '특정 DB 동작이 지정된 버전의 SQL Server 와 호환되도록 설정한다' 라고 나오는데 봐도 뭔말인지 모르겠다만 호환성 지정을 하는 거라고만 알겠다(쨌든 이 옵션의 레벨값은 기본값이 110 인데 내 경우에도 호환성 수준이 높아 아래의 CREATE FUNCTION 을 실행할 수 없다고 에러가 났었다. SQL Server 의 버전에 따라 지정할 수 있는 값이 다른데 2008 의 경우 100 이다. 자세한 내용은 여기 를 참조하면 되겠다).
어셈블리 생성 후 아래와 같이 라이브러리의 함수를 MSSQL 에 외부 참조 생성 해 주어야 한다.
CREATE FUNCTION MSSQL함수명(@파라미터명 타입, ...) RETURNS 반환타입EXTERNAL NAME 어셈블리명.클래스명.CLR함수명 GO
다 만들어졌다.
테스트를 해보니 잘 돌아간다.
