어린왕자와 여우

T-SQL은 대부분의 데이터 처리에서 매우 강력한 기능을 발휘하지만 텍스트 분석 및 조작에 대한 지원은 미흡한 수준입니다. 기본 제공 문자열 함수를사용하여 정교한 텍스트 분석을 수행하려고 시도하면 디버그와 유지 관리가 매우 힘든 방대한 함수 및 저장 프로시저를 만들게 되는 경우가많습니다. 더 좋은 방법은 없는 것일까요?

정규식을 사용하면 훨씬 효과적이고 세련된 솔루션을 구현할 수 있습니다. 정규식은 텍스트를 비교하여 레코드를 식별하는 데도 유용하게 사용할 수 있지만 이외에도 다양한 작업에 사용할 수 있습니다. 여기에서는 SQL Server™ 2000에서는실용적이지않거나불가능했지만 SQL Server 2005에서는 CLR(공용언어런타임) 호스팅지원을통해가능해진다양한작업을수행하는방법을살펴보겠습니다. 이러한작업에는간단한것도있는반면어떤것은매우놀라울것입니다.

SQL에서 정규식이 새로운 기능은 아닙니다. Oracle은 10g에서 처음으로 기본 제공 정규식을 도입했으며 여러 다른 공개 소스 데이터베이스솔루션에서도 일종의 정규식 라이브러리를 사용하고 있습니다. 이전 버전의 SQL Server에서도 정규식을 사용할 수 있었지만 그 과정이효율적이지는 못했습니다.

sp_OACreate 저장 프로시저를 사용하면 정규식을 구현하는 모든 OLE 자동화 개체를 사용할 수 있지만 먼저 COM 개체를 만들고 최소한 한 번IDispatch 호출을 수행한 다음 개체를 제거해야 했습니다. 이러한 방식은 대부분의 용도에 매우 비효율적이고 여러 가지 성능 문제를일으킵니다. 유일한 대안은 확장 저장 프로시저를 만드는 것이었습니다. 그러나 이제는 Microsoft.NET Framework를 사용하여효율적이며 오류가 발생할 우려가 적은 함수 집합을 만들 수 있는 CLR UDF(사용자 정의 함수)인 SQLCLR이 있습니다.

CLR사용자 정의 함수

CLR사용자 정의 함수는 .NET 어셈블리 내에 정의된 정적 메서드(Visual Basic에서는 공유 함수)입니다. SQLCLR 개체를 사용하려면 새 CREATE ASSEMBLY 문을 사용하여 SQL Server에 어셈블리를 등록한 다음 어셈블리 내에서 해당 구현을 가리키는 각 개체를만들어야 합니다. 함수의 경우 CREATE FUNCTION 문은 CLR 사용자 정의 함수 생성을 지원하도록 확장되었습니다. 작업을 더 쉽게하기 위해서 Visual Studio2005에서는 SQL Server 프로젝트를 사용할 때 사용자를 위해 모든 등록 처리를 대신해 줍니다.이러한 종류의 프로젝트는 디버그를 하려고 할 때(또는 디버깅 없이 시작할 때) 프로젝트가 다시 컴파일되고 결과 어셈블리는 물론 여기에 정의된모든 SQLCLR 개체가 SQL Server에 배포 및 등록된다는 점에서 대부분의 Visual Studio 프로젝트와는 차이가 있습니다. 그런다음 IDE는 프로젝트에 지정된 테스트 스크립트를 실행합니다. SQL 스크립트와 .NET 코드에 모두 중단점을 설정할 수 있어 디버깅이 더욱간단해졌습니다.

함수를 추가하는 것은 다른 프로젝트 형식에 새 클래스를 추가하는 것과 같습니다. 프로젝트에 새 항목을 추가하고 메시지가 표시되면 사용자 정의 함수를선택합니다. 그러면 사용자의 모든 함수를 포함하는 부분 클래스에 새 메서드가 추가됩니다. 새 메서드에는 SqlFunction 특성이 적용되며이 메서드는 Visual Studio에서 함수를 등록하는 데 필요한 SQL 문을 만드는 데 사용됩니다. SqlFunction의IsDeterministic, IsPrecise, DataAccess 및 SystemDataAccess 역시 SQL Server에서 다양한 용도로 사용됩니다.

패턴검색

문자열이 패턴과 일치하는지 확인하는 것은 정규식의 가장 간단한 용도이며 그림 1에서 보여 주고 있는 것처럼 손쉽게 수행할 수 있습니다.

Figure1 일치하는 문자열 검색

public static partial class UserDefinedFunctions

{

    public static readonly RegexOptions Options =

        RegexOptions.IgnorePatternWhitespace|

        RegexOptions.Singleline;

    [SqlFunction]

    public static SqlBoolean RegexMatch(

        SqlCharsinput, SqlString pattern)

    {

        Regex regex = new Regex( pattern.Value, Options );

        return regex.IsMatch( new string( input.Value ) );

    }

}

먼저 필자는 Options 필드를 사용하여 함수에 대한 정규식 옵션을 저장했습니다. 이 경우에는 RegexOptions.SingleLine 및RegexOptions.IgnorePatternWhitespace를 선택했습니다. 전자는 한 줄 모드를 지정하며 후자는 정규식에서이스케이프하지 않은 공백을 제거하고 파운드 기호(#)로 표시한 주석을 사용할 수 있도록 합니다. 충분히 고려하고 분석한 뒤에 사용할 수 있는다른 옵션으로 RegexOption.Compiled가 있습니다. 지나치게 많지만 않다면 자주 사용되는 식에 Compiled를 사용하여 상당한성능 향상을 얻을 수 있습니다. 반복적으로 사용되는 식 역시 컴파일해야 합니다. 그러나 시작 비용 및 메모리 오버헤드를 증가시키는 효과가있으므로 가끔 사용되는 정규식에 대해서는 Compiled를 사용하지 마십시오. 이때 식을 컴파일할 것인지 결정하기 위해 일반적인 용도의RegexMatch 함수를 추가 매개 변수로 사용하면 성능 향상이 오버헤드를 감수할 가치가 있는지 확인할 수 있습니다.

RegexOptions를사용하도록 지정한 다음 필자는 SqlString 대신 SqlChars 데이터 형식을 사용하여 RegexMatch 함수를 정의했습니다.SqlString 데이터 형식은 nvarchar(4,000)로 변환되는 반면 SqlChars는 nvarchar(max)로 변환됩니다. 새로운max 크기 기능을 통해 문자열이 SQL Server 2000의 8,000바이트 제한을 벗어날 수 있게 되었습니다. 필자는 이 기사 전반에서범용성과 최대한의 유연성을 위해서 nvarchar(max)를 사용할 것입니다. 그러나 모든 관련 문자열이 4,000개 미만의 문자를 포함한다면nvarchar(4,000)를 사용하는 것이 성능면에서 훨씬 바람직합니다. 따라서 상황에 따른 요구를 감안하여 적절하게 코딩해야 합니다.

메서드의 남은 코드는 간단합니다. 정의한 옵션으로 Regex 인스턴스가 생성되고 제공된 패턴과 IsMatch 메서드를 사용하여 지정한 입력이 패턴과 일치하는지 확인합니다. 이제 간단한 쿼리를 테스트 스크립트에 추가해야 합니다.

select dbo.RegexMatch( N'123-45-6789', N'^\d{3}-\d{2}-\d{4}$' )

이 문에 있는 패턴은 미국 사회 보장 번호에 대한 간단한 테스트를 위한 것입니다. 새 쿼리에 중단점을 설정한 다음 디버깅을 시작하여 함수를단계적으로 실행합니다. 이 함수를 사용하여 여러 다른 테스트를 수행할 수 있지만 여기에서는 일반적으로 잘 고려하지 않는 부분을 살펴보겠습니다.예를 들어 데이터베이스 내에서 명명 규칙을 일관성 있게 유지하는 것은 매우 중요하지만 모든 저장 프로시저가 사용자 조직의 지침을 충족하는지검사하는 쿼리를 작성하기는 어렵습니다. RegexMatch 함수를 사용하면 이 작업을 간단하게 수행할 수 있습니다. 예를 들어 다음 쿼리테스트는 이러한 작업을 수행합니다.

select ROUTINE_NAME

from INFORMATION_SCHEMA.ROUTINES

where ROUTINE_TYPE = N'PROCEDURE'

    and dbo.RegexMatch( ROUTINE_NAME,

        N'^usp_(Insert|Update|Delete|Select)([A-Z][a-z]+)+$') = 0

이 쿼리는 모든 저장 프로시저가 "usp_" 접두사로 시작하며 그 다음 "Insert","Update", "Delete" 또는 "Select"가 나오고, 그 다음 최소한 한개의 엔터티 이름이 있는지 테스트합니다. 이 쿼리는 또한 엔터티의 각 단어가 대문자로 시작하는지도 확인합니다. 이러한 코드 네 줄을 기본 제공함수만 사용한 단순화된 버전과 비교해 보십시오.

select ROUTINE_NAME

from INFORMATION_SCHEMA.ROUTINES

where ROUTINE_TYPE = N'PROCEDURE'

    and ( LEN( ROUTINE_NAME ) < 11

    or LEFT( ROUTINE_NAME, 4 ) <> N'usp_'

    or SUBSTRING( ROUTINE_NAME, 5, 6 ) not in

        (N'Insert', N'Update', N'Delete', N'Select' ) )

코드의 양은 더 많아졌지만 이 쿼리에는 앞서 정규식에 있던 몇 가지 기능이 빠져 있습니다. 첫째, 이 쿼리는 대/소문자를 구분하지 않으며 테스트를수행하기 위해 쿼리 내에서 데이터 정렬을 사용했기 때문에 제어하기 어렵습니다. 둘째, 이 쿼리는 프로시저 이름에 포함된 실제 엔터티 이름에대해서는 전혀 테스트를 수행하지 않고 있습니다. 셋째, 이 쿼리에서 테스트한 문자열 네 개는 모두 여섯 문자 길이였으므로 작업 이름을 나타내는여섯 문자의 부분 문자열을 추출하여 비교하여 코드를 단순화했다는 사실도 문제입니다. 이 예에서는 모든 작업 이름이 여섯 문자 길이였기 때문에문제가 되지 않았지만 "Get", "List" 또는 "Find"와 같이 복잡한 동사를지정하는 표준의 경우를 고려해 보아야 합니다. RegexMatch 함수의 경우에는 단순히 목록에 항목을 추가하면 되기 때문에 이러한 동사도손쉽게 처리할 수 있습니다.

전화번호부터 우편 번호, 사용자 지정 계정 번호 형식에 이르기까지 유효성 검사는 정규식의 매우 일반적인 용도입니다. 다음 테이블 정의에서 보여주고 있는 것처럼 CHECK 제약 조건은 이러한 용도에 최적입니다.

CREATE TABLE [Account]

(

    [AccountNumber]nvarchar(20) CHECK (dbo.RegexMatch(

        [AccountNumber],'^[A-Z]{3,5}\d{5}-\d{3}$' ) = 1),

    [PhoneNumber]nchar(13) CHECK (dbo.RegexMatch(

        [PhoneNumber],'^\(\d{3}\)\d{3}-\d{4}$' ) = 1),

    [ZipCode]nvarchar(10) CHECK (dbo.RegexMatch(

        [ZipCode],'^\d{5}(\-\d{4})?$' ) = 1)

)

이 제약 조건은 3~5자의 문자로 시작하고 5자의 숫자가 이어지며, 다음으로 대시가 오고 마지막으로 3자의 숫자로 이루어진다는 규칙을 사용하여AccountNumber 열의 유효성을 검사합니다. 전화 번호와 우편 번호는 미국 전화 번호 및 우편 번호 형식을 대상으로 유효성을검사했습니다. RegexMatch 함수는 SQL Server에 다양한 기능을 제공하지만 .NET의 정규식 구현은 앞으로 설명하는 내용을 통해알 수 있듯이 더 많은 기능을 제공할 수 있습니다.

데이터추출

문자열에서 데이터를 추출하기 위해 정규식의 그룹화 기능을 사용할 수 있습니다. RegexGroup 함수는 T-SQL에 이러한 기능을 제공합니다.

[SqlFunction]

public static SqlChars RegexGroup(

    SqlChars input, SqlString pattern, SqlString name )

{

    Regex regex = new Regex( pattern.Value, Options );

    Match match = regex.Match( new string( input.Value ) );

    return match.Success ?

        new SqlChars( match.Groups[name.Value].Value ) : SqlChars.Null;

}

이 함수는 RegexMatch 함수와 마찬가지로 Regex 개체를 만들지만 일치 항목을 찾는 테스트 대신 입력 문자열에서 발견된 첫 번째 항목에대한 Match 개체를 만듭니다. Match 개체는 지정된 그룹을 검색하는 데 사용됩니다. 입력에서 일치 항목이 발견되지 않으면 Null 값이반환됩니다. 이름 그룹 대신 번호 그룹을 선호하는 경우에도 이 함수를 그대로 사용할 수 있습니다. SQL 코드에서 함수에 정수 값을 전달하면함수는 이를 명시적으로 nvarchar로 캐스팅하고 적절한 그룹을 반환합니다.

SELECT목록 내에서 RegexGroup 함수를 사용하면 다른 정보의 조각에서 특정한 정보의 조각을 추출할 수 있습니다. 예를 들어 URL을 저장하는열이 있다면 손쉽게 이 URL을 구문 분석하여 개별적인 조각을 확인할 수 있습니다. 이 쿼리에서는 UrlTable 테이블의 Url 열에 저장된 모든 개별 서버를 확인하기 위해 그룹화를 사용합니다.

select distinct dbo.RegexGroup( [Url],

    N'https?://(?<server>([\w-]+\.)*[\w-]+)',N'server' )

from [UrlTable]

계산열 내에서도 이 함수를 사용할 수 있습니다. 다음 테이블 정의는 전자 메일 주소를 사서함과 도메인으로 분리합니다.

CREATE TABLE [Email]

(

    [Address]nvarchar(max),

    [Mailbox]as dbo.RegexGroup( [Address],

        N'(?<mailbox>[^@]*)@',N'mailbox' ),

    [Domain]as dbo.RegexGroup( [Address], N'@(?<domain>.*)', N'domain' )

mailbox열은 전자 메일 주소의 사서함이나 사용자 이름을 반환하며 domain 열은 전자 메일 주소의 도메인을 반환하게 됩니다.

패턴저장소

이러한 함수에서 사용된 모든 패턴은 단순한 문자열이므로 데이터베이스 내의 테이블에 저장할 수 있습니다. 국가별 데이터를 저장하는 대부분의데이터베이스에는 국가를 나타내는 테이블이 있습니다. 이러한 테이블에 약간의 열을 추가하면 국가별로 유효성 검사 패턴을 저장할 수 있을 것이며,이렇게 하면 주소 행에 적용될 제약 조건을 해당 행의 국가에 따라 다르게 적용할 수 있게 됩니다.

고객의 데이터를 저장하는 데이터베이스에는 이미 고객을 나타내는 테이블이 있는 것이 일반적입니다. 이러한 테이블을 사용하면 데이터베이스에 고객의 원본데이터를 저장하는 방식을 지정하는 그룹화 패턴을 저장할 수 있으며 이렇게 하면 클라이언트 데이터에서 실제 필요한 데이터를 가져오기 위한 계산열을 만들 수 있습니다. 예를 들어 사용자의 고객이 각자 고유한 계정 번호 방식을 사용하는 경우 이러한 계정 번호에서 특정한 부분이 필요하다면각 고객의 정보에서 필요한 부분을 가져오는 정규식을 손쉽게 만들 수 있습니다.

일치

문자열이 패턴과 일치하는지 확인하는 대신 모든 일치 항목을 추출하기를 원하는 경우도 있습니다. 이전에 이러한 유형의 추출을 수행하려면 커서를 사용하여문자열 부분에 대해 반복적으로 작업해야 했습니다. 이러한 처리는 느릴 뿐만 아니라 코드를 이해하고 관리하기도 어려웠습니다. 정규식은 이러한작업을 수행할 수 있는 훨씬 좋은 방법입니다. 문제는 필요한 모든 데이터를 SQL 구문 내에 어떻게 반환하는가 하는 것입니다. 해답은 테이블값 함수입니다.

테이블값 함수는 이전 함수와 어느 정도 비슷하지만 두 가지 독특한 차이점이 있습니다. 첫째, 메서드에 적용되는 특성은 반드시 반환되는 테이블의구조를 완전하게 선언해야 합니다. 둘째, 두 가지 메서드가 관련되어 있습니다. 첫 번째 메서드는 함수의 실제 결과가 아닌 열거 가능한 개체를반환하며 두 번째 메서드는 각 행의 필드를 채우기 위해 열거된 개체에 전달됩니다. 열거자를 통해 가져온 각각의 값은 결과 집합의 각 행에대응합니다. .NET Framework의 ICollection 인터페이스는 IEnumerable을 구현하므로 첫 번째 메서드를 사용하여 어떤컬렉션이든지 반환할 수 있습니다. Regex 클래스에는 사용 가능한 MatchCollection을 반환하는 Matches 메서드가 있습니다.MatchCollection과 관련된 문제는 Matches 메서드가 반환하기 이전에 전체 문자열을 처리해야 한다는 것입니다. SQLServer에는 프로세스 발생에 따라 처리되는 최적화 기능이 포함되어 있으므로 필자는 전체 컬렉션을 미리 반환하기보다는 요청에 따라 각 일치항목을 반환하는 자체 열거자를 작성하는 방법을 선호합니다. 이러한 결정은 함수가 사용되는 방법에 따라 달라지므로 열거자를 최적화하기 전에충분한 테스트를 거쳐야 합니다.

그림2의 코드에서는 열거자를 보여 주고 있습니다. MatchNode 클래스는 문자열 내의 개별적인 일치 항목을 래핑하며 반환된 일치 항목의집합에서 해당 항목의 위치를 추적합니다. MatchIterator 클래스는 열거 가능하며 정규식 처리를 수행합니다. 이 클래스는 이전 버전의프레임워크보다 훨씬 손쉽게 열거형을 만들기 위해 새로운 yield 키워드를 사용하며 요청된 입력 문자열 내에서 찾아낸 각 일치 항목을 반환합니다.

Figure2 일치 항목을 찾기 위한 사용자 지정 열거 가능 개체

internal class MatchNode

{

    private int _index;

    public int Index { get{ return _index; } }

    private string _value;

    public string Value { get { return _value; } }

    public MatchNode( int index, string value )

    {

        _index= index;

        _value= value;

    }

}

internal class MatchIterator : IEnumerable

{

    private Regex _regex;

    private string _input;

    public MatchIterator( string input, string pattern )

    {

        _regex= new Regex( pattern, UserDefinedFunctions.Options );

        _input= input;

    }

    public IEnumerator GetEnumerator()

    {

        int index = 0;

        Match current = null;

        do

        {

            current= (current == null) ?

                _regex.Match(_input ) : current.NextMatch( );

            if(current.Success)

            {

                yieldreturn new MatchNode( ++index, current.Value );

            }

        }

        while(current.Success);

    }

}

그림3의 코드는 테이블 값 CLR UDF를 정의합니다. RegexMatches 메서드는 새 MatchIterator를 반환하며RegexMatches 메서드의 SqlFunctionAttribute에도 또한 약간의 추가 속성이 있습니다.

TableDefinition속성은 함수의 테이블 정의로 설정됩니다. FillRowMethodName은 반환되는 열거 가능 개체의 각 반복 시 호출할 메서드의 이름으로설정됩니다. 이 경우 호출할 메서드는 FillMatchRow입니다.

Figure3 일치 항목을 찾기 위한 테이블 값 CLR UDF

[SqlFunction(FillRowMethodName = "FillMatchRow",

    TableDefinition= "[Index] int,[Text] nvarchar(max)" )]

public static IEnumerable RegexMatches(SqlChars input, SqlString pattern)

{

    return new MatchIterator( new string( input.Value ), pattern.Value );

}

[SuppressMessage("Microsoft.Design", "CA1021:AvoidOutParameters" )]

public static void FillMatchRow( object data,

    out SqlInt32 index, out SqlChars text )

{

    Match Nodenode = (MatchNode)data;

    index= new SqlInt32( node.Index );

    text= new SqlChars( node.Value.ToCharArray( ) );

}

각 MatchIterator 반복에 대해 FillMatchRow 메서드의 첫 번째 인수로 MatchNode가 전달됩니다. FillMatchRow메서드의 나머지 매개 변수는 출력 매개 변수로 선언해야 하며 첫 번째 함수에 정의된 테이블 정의와 일치해야 합니다. FillMatchRow함수는 간단히 MatchNode의 속성을 사용하여 필드 데이터를 채웁니다.

이 함수를 사용하면 마침내 문자열에서 여러 데이터 조각을 손쉽게 추출할 수 있습니다. RegexMatches 함수의 사용법을 설명하기 위해서 다음쿼리를 사용하여 문자열 내에 포함된 고유 단어의 수를 확인해 보겠습니다.

declare @text nvarchar(max), @pattern nvarchar(max)

select

    @text= N'Here are four words.',

    @pattern= '\w+'

select count(distinct [Text])

from dbo.RegexMatches( @text, @pattern )

이 예제는 매우 간단합니다. 함수의 잠재력을 보여 주는 이 예제에서는 고유 키워드를 제거함으로써 문자열의 전체 단어 수를 반환하고 있습니다.텍스트 입력을 임의의 길이로 제한하는 웹 사이트를 많이 볼 수 있는데 이러한 테스트를 새로운 nvarchar(max) 표기법과 결합하면 입력단어 수를 제한하는 것도 가능합니다. 이러한 유형의 쿼리는 다양한 분석적인 처리 요구에 사용할 수 있지만 더 일반적인 작업에는RegexMatches 함수도 사용할 수 있습니다. 그러나 이러한 유형의 쿼리는 정규식을 과도하게 사용한 것으로 볼 수 있습니다. 이 예에서"\w+" 식으로 수행한 분할 작업은 String.Split 메서드를 사용하면 더 빠르고 쉽게 수행할 수 있습니다. 정규식은매우 강력한 도구임이 분명하지만 합당한 이유가 있을 때만 사용해야 합니다. 더 나은 성능을 제공하는 더 간단한 도구가 있을 수 있습니다.

저장프로시저에 값 목록을 전달하는 방법을 묻는 질문을 MSDN포럼에서 자주 보게 됩니다. 또한 실제로 관련된 레코드를 확인하기 위해 이러한 목록을실제 목록으로 구문 분석하는 복잡한 메서드도 많이 보았습니다. RegexMatches 함수를 사용하면 이 문제를 훨씬 깔끔하게 해결할 수 있습니다.

declare @pattern nvarchar(max), @list nvarchar(max)

select @pattern = N'[^,]+', @list = N'2,4,6'

select d.* from [Data] d

inner join dbo.RegexMatches( @list, @pattern ) re

on d.[ID] = re.[Text]

이 패턴은 쉼표를 포함하지 않는 모든 문자 그룹을 찾습니다. 이 쿼리는 ID라는 정수 열이 있는 Data라는 테이블에서 각 레코드를 찾아 목록으로반환합니다. SQL Server 내의 암시적 캐스팅 기능을 고려하면 더욱 유용하게 사용할 수 있습니다. 동일한 쿼리를 정수, 날짜/시간,GUID 또는 부동 소수점 데이터 형식에 사용할 수 있습니다. 다른 방법으로 이 정도 수준의 유연성을 발휘하여 값 목록을 처리하려면 여러 개의함수 또는 저장 프로시저를 사용해야 할 것입니다. 이 함수는 또한 쉼표로 구분된 목록 이외의 다른 목록에도 사용할 수 있습니다. 공백,세미콜론, 탭, 캐리지 리턴 또는 다른 식별 가능한 문자를 사용하여 분리된 목록을 처리할 수 있습니다.

일치항목 내에서의 데이터 추출

일치항목을 반환하는 것과 비슷하게 각 일치 항목에서 데이터를 추출할 수 있습니다. SQL을 사용하여 이를 구현하기는 매우 어려우며 일반적으로이러한 작업은 데이터베이스 대신 응용 프로그램 내에서 구현됩니다. 그러나 이렇게 하면 데이터베이스를 사용하는 각 응용 프로그램에서 필요한처리를 구현해야 하므로 문제가 될 수 있습니다. 이러한 시나리오에서 합리적인 방식은 이 기능을 저장 프로시저 내에 구현하는 것입니다.

RegexMatches구현과 마찬가지로 필자는 그룹 정보를 반환하는 데 사용자 지정 열거 가능 개체를 선호합니다. 또한 각 일치 항목 내에서 그룹에 대해 반복해야하므로 그룹화는 약간 더 복잡해집니다. 그림 4에서 GroupNode 클래스는 해당 그룹의 이름도 포함한다는 점을 제외하고는MatchNode와 비슷합니다. GroupIterator 클래스는 MatchIterator 클래스와 비슷하지만 각 그룹을 반환하기 위한 추가루프를 포함합니다. 이제 열거 가능 개체가 있으므로 RegexMatches 함수에서 했던 것처럼 테이블 값 함수를 정의할 차례입니다.

Figure4 그룹용 사용자 지정 열거 가능 개체

internal class GroupNode

{

    private int _index;

    public int Index { get { return _index; } }

    private string _name;

    public string Name { get { return _name; } }

    private string _value;

    public string Value { get { return _value; } }

    public GroupNode( int index, string group, string value )

    {

        _index= index;

        _name= group;

        _value= value;

    }

}

internal class GroupIterator : IEnumerable

{

    private Regex _regex;

    private string _input;

    public GroupIterator( string input, string pattern )

    {

        _regex= new Regex( pattern, UserDefinedFunctions.Options );

        _input= input;

    }

    public IEnumerator GetEnumerator()

    {

        int index = 0;

        Match current = null;

        string[] names = _regex.GetGroupNames();

        do

        {

            index++;

            current= (current == null) ?

                _regex.Match(_input ) : current.NextMatch( );

            if(current.Success)

            {

                foreach(stringname in names)

                {

                    Group group = current.Groups[name];

                    if(group.Success)

                    {

                        yieldreturn new GroupNode(

                            index,name, group.Value );

                    }

                }

            }

        }

        while(current.Success);

    }

}

그림5에서 RegexGroups 함수는 일치 항목 내의 그룹 이름을 포함하는 추가 데이터 열을 반환한다는 점을 제외하고는 RegexMatches함수와 비슷하게 정의되었습니다. 이제 이 함수를 사용하여 문자열 내에서 여러 일치 항목을 찾고 각 일치 항목 내에서 특정한 정보 조각을 추출할수 있습니다.

Figure5 그룹용 테이블 값 CLR UDF

[SqlFunction(FillRowMethodName = "FillGroupRow", TableDefinition =

    "[Index]int,[Group] nvarchar(max),[Text] nvarchar(max)" )]

public static IEnumerable

    RegexGroups(SqlChars input, SqlString pattern )

{

    return new GroupIterator( new string( input.Value ), pattern.Value );

}

[SuppressMessage("Microsoft.Design", "CA1021:AvoidOutParameters" )]

public static void FillGroupRow( object data,

    out SqlInt32 index, out SqlChars group, out SqlChars text )

{

    GroupNodenode = (GroupNode)data;

    index= new SqlInt32( node.Index );

    group= new SqlChars( node.Name.ToCharArray( ) );

    text= new SqlChars( node.Value.ToCharArray( ) );

}

다양한 형식의 데이터를 가져오는 작업은 데이터베이스를 처리할 때 공통적인 작업이며 쉼표로 구분된 형식의 파일을 가져오는 작업은 지극히 반복적으로이루어집니다. 대부분의 개발자는 각 행을 처리하고 데이터를 추출한 다음, 각 행에 대해 저장 프로시저를 실행하도록 응용 프로그램을 작성합니다.이러한 처리 방식도 문제는 없지만 필자는 다른 솔루션을 제안하고자 합니다. 전체 파일을 저장 프로시저로 전달하고 저장 프로시저가 완벽히 처리할수 있다면 어떻겠습니까? 이러한 아이디어는 일반적으로 구현하기 너무 복잡하다고 생각하기 쉽지만 RegexGroups 함수를 사용하면 쿼리 한개로 이러한 삽입을 수행할 수 있습니다. 예를 들어 다음과 같은 고객 데이터가 있다고 가정해 보겠습니다.

2309478,JanetLeverling,J

2039748,NancyDavolio,N

0798124,AndrewFuller,M

4027392,RobertKing,L

여기에서 각 행에 숫자 7자로 된 고객 번호, 고객 이름, 그리고 한 문자로 된 고객 유형이라는 세 가지 유형의 정보가 필요한 경우 다음 정규식을사용하면 이 세 가지 정보를 모두 추출할 수 있습니다.

(?<CustomerNumber>\d{7}),(?<CustomerName>[^,]*),(?<CustomerType>[A-Z])\r?\n

아직 남은 문제라면 RegexGroups 함수에서 반환된 결과를 곧바로 사용할 수 없다는 점입니다. 커서를 사용하여 결과 내에서 반복하는 대신SQL Server 2005의 피벗 기능을 사용할 수 있습니다. 이 모두를 저장 프로시저에 통합하면 필요한 모든 것이 준비됩니다. 그림 6의저장 프로시저는 쉼표로 구분된 최대 2GB의 유니코드 데이터를 포함하는 파일의 전체 텍스트를 받습니다. 그런 다음 전체 파일을 처리하여 파일의각 줄을 Customer 테이블의 한 행으로 삽입합니다. 구분 문자가 있는 텍스트 파일은 어떠한 것이라도 같은 방법으로 처리할 수 있습니다.패턴에 약간의 변화를 적용하면 문자열 내의 쉼표를 지원하도록 이스케이프 시퀀스를 추가할 수도 있습니다.

Figure6 쉼표로 구분된 파일 처리

(?<CustomerNumber>\d{7}),(?<CustomerName>[^,]*),(?<CustomerType>[A-Z])\r?\n

물론 이 경우에도 동일한 작업을 수행하는 여러 가지 다른 방법이 있으며 때로는 정규식이 최선의 옵션이 아닐 수 있습니다. 이 예에서 피벗을 사용하게되면 데이터를 특수 그룹 형식으로 반환하기 위해 RegexGroups에서 수행한 모든 작업이 취소됩니다. 각 줄을 읽고 쉼표를 기준으로String.Split을 수행한 다음 각 행을 반환하는 훨씬 간단하고 빠른 TVF를 사용하여 테이블에 직접 데이터를 삽입할 수 있습니다.

결론

이러한 일치 함수는 강력하지만 완벽한 것은 아닙니다. 일치가 수행되는 정확한 방법을 결정하는 옵션이 많이 있습니다. 데이터베이스 데이터 정렬이대/소문자를 구분하는 경우 함수에서 대/소문자를 구분하도록 일치를 수행할 수 있습니다. 결과 집합을 줄이기 위해서 명시적인 캡처 옵션이 필요할수 있으며 여러 줄 옵션을 사용하면 일부 작업을 위해 더 정확한 패턴을 만들 수 있습니다. 사용자 정의 형식을 만들어 각 함수에 필요한 정확한옵션을 전달함으로써 함수를 실행할 때마다 다른 옵션 집합을 사용하기를 원할 수도 있습니다.

텍스트를 처리할 때는 지역화 문제가 있다는 사실을 인식해야 합니다. 예를 들어 .NET Framework Regex 클래스는 필자의 예에서 사용된 라틴문자 이외의 많은 다양한 문자를 처리할 수 있으므로 국가별 데이터를 사용하는 데이터베이스용 패턴을 개발할 때는 주의를 기울여야 합니다.

그리고 이 기사에서도 몇 차례 언급한 것처럼 정규식이 강력한 기능을 가진 것은 사실이지만 실제로 이러한 기능이 필요한지 고려해야 합니다. 기본적인도구 집합을 사용하여 신속하고 간단하게 수행할 수 있는 작업도 있습니다.

기사에서 제공한 예제에는 단순함을 위해서 유효성 검사 및 오류 처리가 생략되어 있지만 프로덕션 시스템에는 이러한 처리가 반드시 필요합니다. 함수의 각입력에 대해 반드시 유효성을 검사해야 하며 Null 또는 비어 있는 문자열 입력에 대한 응답이 요구 사항에 맞는지 확인해야 합니다. Regex클래스는 패턴을 구문 분석할 수 없거나 옵션이 잘못된 경우 예외를 Throw할 수 있습니다. 이러한 예외는 매끄럽게 처리해야 합니다.

정규식을 SQL과 결합함으로써 데이터 처리를 위한 다양한 대안을 마련할 수 있습니다. 이러한 함수를 사용하면 데이터베이스에 기능을 추가하는 데 필요한시간을 단축하고 시스템을 좀 더 유지 관리하기 쉽게 만들 수 있습니다. 모든 데이터베이스에 정규식을 사용할 수 있으며 이러한 함수를 실험하여 새롭고 더 창조적인 용도를 찾아보기를 권합니다.

출처 : http://www.50001.com/tt/board/ttboard.cgi?act=read&db=20503&sortby=title&order=desc&page=1&idx=154