LoginSignup
0
2

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

@Inada_Tomonori(稲田)

SQLServerで使う正規表現関数の作成

Last updated at Posted at 2025-04-06

2025/04/13　追記
末尾にFindMatchesRegexAsTable2の定義を追加

はじめに

SQLServerでVB.NETと同じ正規表現の処理ができる関数を作成します。

VB.NETでのコーディング

キーファイルを作成

次のスクリプトで署名ファイルを作成する。
作成に当たっては、次のサイトを参照しました。
PowerShell でコード署名用の証明書を作成する

# 次のサイトから作成したスクリプト
# https://operationslab.wordpress.com/2014/12/16/powershell-%E3%81%A7%E3%82%B3%E3%83%BC%E3%83%89%E7%BD%B2%E5%90%8D%E7%94%A8%E3%81%AE%E8%A8%BC%E6%98%8E%E6%9B%B8%E3%82%92%E4%BD%9C%E6%88%90%E3%81%99%E3%82%8B/


# サブジェクト
$subject = "CN=everyone@localhost.localhost"
 
# フレンドリ名
$fn = "LOCALHOST Code Signing"
 
# Enhanced Key Usage
# 日本語環境の場合は、日本語又はOIDで指定
# 複数指定する場合は、カンマ区切り(配列)
# $EKU = [Security.Cryptography.Oid[]]("サーバー認証", "クライアント認証")
$eku = [Security.Cryptography.Oid[]]"コード署名"
 
# Key Usage
# 日本語環境であっても、英語指定
# 複数指定する場合は、カンマ区切りの文字列
# DigitalSignature (bit0) = 電子署名
# NonRepudiation (bit1) = 否認防止
# KeyEncipherment (bit2) = 鍵暗号
# DataEnciphermemt (bit3) = データ暗号
# KeyAgreement (bit4) = 鍵交換
# KeyCertSign (bit5) = 電子証明書の検証
# CRLSign (bit6) = CRLの署名検証
# EncipherOnly (bit7) = 鍵交換時のデータ暗号用
# DecipherOnly (bit8) = 鍵交換時のデータ復号用
$ku = [Security.Cryptography.X509Certificates.X509KeyUsageFlags]"KeyEncipherment, DigitalSignature"
 
# 有効期限
# NotBefore
$start = [DateTime]::Now.AddDays(-1)
# NotAfter
$end = $start.AddYears(20)
 
 
# アルゴリズムと鍵長
$provider = "Microsoft Enhanced Cryptographic Provider v1.0"
$kalgo = [Security.Cryptography.Oid]"RSA"
$klength = 2048
$halgo = [Security.Cryptography.Oid]"SHA1"
 
# X509KeySpec
# 1: The key can be used to encrypt (including key exchange) or sign depending on the algorithm. For RSA algorithms, if this value is set, the key can be used for both signing and encryption.
# 2: The key can be used for signing.
$kspec = 2
 
# 保存先
# 現在のユーザーの「個人」証明書ストアに保存
$slocation = [Security.Cryptography.X509Certificates.StoreLocation]"CurrentUser"
$sname = [Security.Cryptography.X509Certificates.StoreName]"My"
 
$oidlist = New-Object -ComObject X509Enrollment.CObjectIDs
$eku | % {
    $o = New-Object -ComObject X509Enrollment.CObjectID
    $o.InitializeFromValue($_.Value)
    $oidlist.Add($o)
}
 
$x509eku = New-Object -ComObject X509Enrollment.CX509ExtensionEnhancedKeyUsage
$x509eku.InitializeEncode($oidlist)
 
$x509ku = New-Object -ComObject X509Enrollment.CX509ExtensionKeyUsage
$x509ku.InitializeEncode([int]$ku)
$x509ku.Critical = $true
 
$algo = New-Object -ComObject X509Enrollment.CObjectId
$algo.InitializeFromValue($kalgo.Value)
 
$key = New-Object -ComObject X509Enrollment.CX509PrivateKey
$key.ProviderName = $provider
$key.Algorithm = $algo
$key.Length = $klength
$key.KeySpec = $kspec
# オリジナルはエクスポート不可能
#$key.ExportPolicy = 0
$key.ExportPolicy = 1
$key.MachineContext = $false
$key.Create()
 
$dn = New-Object -ComObject X509Enrollment.CX500DistinguishedName
$dn.Encode($subject, 0)
 
$hash = New-Object -ComObject X509Enrollment.CObjectId
$hash.InitializeFromValue($halgo.Value)
 
$req = New-Object -ComObject X509Enrollment.CX509CertificateRequestCertificate
$req.InitializeFromPrivateKey(1, $key, "")
$req.Subject = $dn
$req.Issuer = $dn
$req.NotBefore = $start
$req.NotAfter = $end
$req.SignatureInformation.HashAlgorithm = $hash
$req.X509Extensions.Add($x509eku)
$req.X509Extensions.Add($x509ku)
$req.Encode()
 
$enroll = New-Object -ComObject X509Enrollment.CX509enrollment
$enroll.InitializeFromRequest($req)
$enroll.CertificateFriendlyName = $fn
$enroll.InstallResponse(2, $enroll.CreateRequest(1), 1, "")

個人フォルダの署名をエクスポートして署名ファイルを作成する。

VBのソースを入力

VisualStudioで適宜プロジェクトを作成して、次のコードを入力してください。
アセンブリに署名をするため、署名ファイルをキーファイルに設定する。

Imports System.Data.SqlTypes
Imports System.Xml '不要そうに見えるけれど必要
Imports Microsoft.SqlServer.Server

'Namespace nsRegexFunctionOnSqlServer
''' <summary>
''' 注意：　C++ではNamespaceで宣言した名前空間でSQLServerに登録できるが、
''' VB.NETでは、プロジェクトのルート名前空間で定義した名前空間が使用され、
''' namespaceで宣言すると２重に宣言したようになる。
''' 分かりやすさを考えると、namespaceでは宣言しない方がよい
''' </summary>
Public Class ClsCLRRegexFunction

    ''' <summary>
    ''' ターゲット文字列がテストパターンと一致するか判定する関数
    ''' </summary>
    ''' <param name="vTargetStr">対象の文字列</param>
    ''' <param name="vTestPattern">テストパターン</param>
    ''' <returns>一致する場合はTrueを返す</returns>
    <SqlFunction(
        DataAccess:=DataAccessKind.None,
        IsDeterministic:=True,
        IsPrecise:=False,
        SystemDataAccess:=SystemDataAccessKind.None,
        Name:="TestRegex"
    )>
    Public Shared Function TestRegex(ByVal vTargetStr As SqlString, ByVal vTestPattern As SqlString) As SqlBoolean
        Dim ans As SqlBoolean
        If vTargetStr.IsNull Or vTestPattern.IsNull Then
            ans = SqlBoolean.Null
        Else
            Dim bool As Boolean = Text.RegularExpressions.Regex.IsMatch(vTargetStr.Value, vTestPattern.Value)
            If IsNothing(bool) Then
                ans = SqlBoolean.Null
            Else
                ans = New SqlBoolean(bool)
            End If
        End If
        Return ans
    End Function

    ''' <summary>
    ''' ターゲット文字列のうちテストパターンと一致する部分を置き換え用文字列に置換する関数
    ''' </summary>
    ''' <param name="vTargetStr">対象の文字列</param>
    ''' <param name="vTestPattern">テストパターン</param>
    ''' <param name="vReplaceStr">置き換え用文字列</param>
    ''' <returns>置換後の文字列</returns>
    <SqlFunction(
        DataAccess:=DataAccessKind.None,
        IsDeterministic:=True,
        IsPrecise:=False,
        SystemDataAccess:=SystemDataAccessKind.None,
        Name:="ReplaceRegex"
    )>
    Public Shared Function ReplaceRegex(ByVal vTargetStr As SqlString, ByVal vTestPattern As SqlString, ByVal vReplaceStr As SqlString) As SqlString
        Dim ans As SqlString
        If vTargetStr.IsNull Or vTestPattern.IsNull Or vReplaceStr.IsNull Then
            ans = SqlString.Null
        Else
            Dim str As String = Text.RegularExpressions.Regex.Replace(vTargetStr.Value, vTestPattern.Value, vReplaceStr.Value)
            If IsNothing(str) Then
                ans = SqlString.Null
            Else
                ans = New SqlString(str)
            End If
        End If
        Return ans
    End Function

    ''' <summary>
    ''' ターゲット文字列のうちテストパターンと一致する文字列を抽出する関数
    ''' </summary>
    ''' <param name="vTargetStr">対象の文字列</param>
    ''' <param name="vTestPattern">テストパターン</param>
    ''' <returns>抽出した文字列</returns>
    <SqlFunction(
        DataAccess:=DataAccessKind.None,
        IsDeterministic:=True,
        IsPrecise:=False,
        SystemDataAccess:=SystemDataAccessKind.None,
        Name:="FindRegex"
    )>
    Public Shared Function FindRegex(ByVal vTargetStr As SqlString, ByVal vTestPattern As SqlString) As SqlString
        Dim ans As SqlString
        If vTargetStr.IsNull Or vTestPattern.IsNull Then
            ans = SqlString.Null
        Else
            Dim str As String = Text.RegularExpressions.Regex.Match(vTargetStr.Value, vTestPattern.Value).Value
            If IsNothing(str) Then
                ans = SqlString.Null
            Else
                ans = New SqlString(str)
            End If
        End If
        Return ans
    End Function

    ''' <summary>
    ''' ターゲット文字列のうちテストパターンと一致する文字列の一部を抽出する関数
    ''' </summary>
    ''' <param name="vTargetStr">対象の文字列</param>
    ''' <param name="vTestPattern">テストパターン</param>
    ''' <param name="vIndex">一致部分の番号</param>
    ''' <param name="vGroupIndex">一致部位のグループインデクス</param>
    ''' <returns>抽出した文字列</returns>
    <SqlFunction(
        DataAccess:=DataAccessKind.None,
        IsDeterministic:=True,
        IsPrecise:=False,
        SystemDataAccess:=SystemDataAccessKind.None,
        Name:="FindMatchesRegex"
    )>
    Public Shared Function FindMatchesRegex(ByVal vTargetStr As SqlString, ByVal vTestPattern As SqlString, ByVal vIndex As SqlInt32, ByVal vGroupIndex As SqlInt32) As SqlString
        Dim ans As SqlString
        If vTargetStr.IsNull Or vTestPattern.IsNull Or vIndex.IsNull Or vGroupIndex.IsNull Then
            ans = SqlString.Null
        Else
            Dim str As String = ""
            Dim m As Text.RegularExpressions.MatchCollection = Text.RegularExpressions.Regex.Matches(vTargetStr.Value, vTestPattern.Value)
            If vIndex.Value >= 1 AndAlso vIndex.Value <= m.Count Then
                Dim g As Text.RegularExpressions.GroupCollection = m.Item(vIndex.Value - 1).Groups
                If vGroupIndex.Value >= 0 AndAlso vGroupIndex.Value < g.Count Then
                    str = g.Item(vGroupIndex.Value).Value()
                End If
            End If
            If IsNothing(str) Then
                ans = SqlString.Null
            Else
                ans = New SqlString(str)
            End If
        End If
        Return ans
    End Function

    Private Class ElementOfFindMatches
        Private ReadOnly _MatchIndex As SqlInt32
        Private ReadOnly _GroupIndex As SqlInt32
        Private ReadOnly _CaptureIndex As SqlInt32
        Private ReadOnly _MatchOffset As SqlInt32
        Private ReadOnly _GroupOffset As SqlInt32
        Private ReadOnly _CaptureOffset As SqlInt32
        Private ReadOnly _TargetWord As SqlString

        Public ReadOnly Property MatchIndex() As SqlInt32
            Get
                Return _MatchIndex
            End Get
        End Property
        Public ReadOnly Property GroupIndex() As SqlInt32
            Get
                Return _GroupIndex
            End Get
        End Property
        Public ReadOnly Property CaptureIndex As SqlInt32
            Get
                Return _CaptureIndex
            End Get
        End Property
        Public ReadOnly Property MatchOffset As SqlInt32
            Get
                Return _MatchOffset
            End Get
        End Property
        Public ReadOnly Property GroupOffset As SqlInt32
            Get
                Return _GroupOffset
            End Get
        End Property
        Public ReadOnly Property CaptureOffset As SqlInt32
            Get
                Return _CaptureOffset
            End Get
        End Property
        Public ReadOnly Property TargetWord() As SqlString
            Get
                Return _TargetWord
            End Get
        End Property

        Public Sub New(
                      MatchIndex As SqlInt32,
                      GroupIndex As SqlInt32,
                      CaptureIndex As SqlInt32,
                      MatchOffset As SqlInt32,
                      GroupOffset As SqlInt32,
                      CaptureOffset As SqlInt32,
                      TargetWord As SqlString)
            _MatchIndex = MatchIndex
            _GroupIndex = GroupIndex
            _CaptureIndex = CaptureIndex
            _MatchOffset = MatchOffset
            _GroupOffset = GroupOffset
            _CaptureOffset = CaptureOffset
            _TargetWord = TargetWord
        End Sub
    End Class

    ''' <summary>
    ''' ターゲット文字列のうちテストパターンと一致する文字列の一部を抽出してテーブルに展開する関数
    ''' </summary>
    ''' <param name="vTargetStr">対象の文字列</param>
    ''' <param name="vTestPattern">テストパターン</param>
    ''' <returns>抽出した文字列</returns>
    <SqlFunction(
        DataAccess:=DataAccessKind.None,
        IsDeterministic:=True,
        IsPrecise:=False,
        SystemDataAccess:=SystemDataAccessKind.None,
        Name:="FindMatchesRegexAsTable",
        FillRowMethodName:="FindMatchesRegexAsTable_FillRow",
        TableDefinition:="MatchIndex int,GroupIndex int,CaptureIndex int,MatchOffset int,GroupOffset int,CaptureOffset int,TargetWord nvarchar(max)"
    )>
    Public Shared Function FindMatchesRegexAsTable(ByVal vTargetStr As SqlString, ByVal vTestPattern As SqlString) As IEnumerable
        Dim ans As ArrayList
        If vTargetStr.IsNull Or vTestPattern.IsNull Then
            ans = New ArrayList '空の集合
        Else
            ans = New ArrayList
            Dim m As Text.RegularExpressions.MatchCollection = Text.RegularExpressions.Regex.Matches(vTargetStr.Value, vTestPattern.Value)
            For mi As Integer = 0 To m.Count - 1
                'ans.Add(
                '        New ElementOfFindMatches(
                '            MatchIndex:=mi + 1,
                '            GroupIndex:=0,
                '            CaptureIndex:=0,
                '            MatchOffset:=m(mi).Index + 1,
                '            GroupOffset:=SqlInt32.Null,
                '            CaptureOffset:=SqlInt32.Null,
                '            TargetWord:=m(mi).Value))
                For gi As Integer = 0 To m(mi).Groups.Count - 1
                    'ans.Add(
                    '    New ElementOfFindMatches(
                    '        MatchIndex:=mi + 1,
                    '        GroupIndex:=gi + 1,
                    '        CaptureIndex:=0,
                    '        MatchOffset:=m(mi).Index + 1,
                    '        GroupOffset:=m(mi).Groups(gi).Index + 1,
                    '        CaptureOffset:=SqlInt32.Null,
                    '        TargetWord:=m(mi).Groups(gi).Value))
                    For ci As Integer = 0 To m(mi).Groups(gi).Captures.Count - 1
                        ans.Add(
                        New ElementOfFindMatches(
                            MatchIndex:=mi + 1,
                            GroupIndex:=gi + 1,
                            CaptureIndex:=ci + 1,
                            MatchOffset:=m(mi).Index + 1,
                            GroupOffset:=m(mi).Groups(gi).Index + 1,
                            CaptureOffset:=m(mi).Groups(gi).Captures(ci).Index + 1,
                            TargetWord:=m(mi).Groups(gi).Captures(ci).Value))
                    Next
                Next
            Next
        End If
        Return ans
    End Function

    Public Shared Sub FindMatchesRegexAsTable_FillRow(obj As Object,
            ByRef MatchIndex As SqlInt32,
            ByRef GroupIndex As SqlInt32,
            ByRef CaptureIndex As SqlInt32,
            ByRef MatchOffset As SqlInt32,
            ByRef GroupOffset As SqlInt32,
            ByRef CaptureOffset As SqlInt32,
            ByRef TargetWord As SqlString)
        Dim elem As ElementOfFindMatches = CType(obj, ElementOfFindMatches)
        MatchIndex = elem.MatchIndex
        GroupIndex = elem.GroupIndex
        CaptureIndex = elem.CaptureIndex
        MatchOffset = elem.MatchOffset
        GroupOffset = elem.GroupOffset
        CaptureOffset = elem.CaptureOffset
        TargetWord = elem.TargetWord
    End Sub
End Class

'End Namespace

ビルドしたDLLファイルをSQLServerをサービスしているサーバにコピーする。

SQLServerでのCLR作成

まず、CLRを使えるようにする。

.sql
sp_configure 'clr enabled', 1;
GO
RECONFIGURE;
GO

署名を取り込む。

.sql
USE [master];
GO

-- 非対称キーの登録
CREATE ASYMMETRIC KEY EVERYONEAsymKey_CLRAssembliesCert 
FROM EXECUTABLE FILE = 'D:\_workspace\SQLServerUserDefinedFunction\CLRRegexFunction.dll';
-- 非対称キーにひもついたログインの登録
CREATE LOGIN EVERYONELogin_CLRAssembliesCert FROM ASYMMETRIC KEY EVERYONEAsymKey_CLRAssembliesCert;
-- ログインに権限を追加
GRANT UNSAFE ASSEMBLY TO EVERYONELogin_CLRAssembliesCert;   
GO

アセンブリから関数を登録する。

.sql
-- アセンブリの登録
CREATE ASSEMBLY CLRRegexFunction
FROM 'D:\_workspace\SQLServerUserDefinedFunction\CLRRegexFunction.dll'
WITH permission_set = safe;
GO

-- 正規表現で一致の判定をする関数
CREATE FUNCTION dbo.CLR_Regex_Test(
    @vTargetStr NVARCHAR(MAX)
	,@vTestPattern NVARCHAR(MAX)
)
RETURNS BIT
AS EXTERNAL NAME CLRRegexFunction.[nsRegexFunction.ClsCLRRegexFunction].TestRegex;
GO

-- 正規表現で文字列の置き換えする関数
CREATE FUNCTION dbo.CLR_Regex_Replace(
    @vTargetStr NVARCHAR(MAX)
	,@vTestPattern NVARCHAR(MAX)
	,@vReplaceStr NVARCHAR(MAX)
)
RETURNS NVARCHAR(MAX)
AS EXTERNAL NAME CLRRegexFunction.[nsRegexFunction.ClsCLRRegexFunction].ReplaceRegex;
GO

-- 正規表現で一致する文字列を抽出する関数
CREATE FUNCTION dbo.CLR_Regex_Find(
    @vTargetStr NVARCHAR(MAX)
	,@vTestPattern NVARCHAR(MAX)
)
RETURNS NVARCHAR(MAX)
AS EXTERNAL NAME CLRRegexFunction.[nsRegexFunction.ClsCLRRegexFunction].FindRegex;
GO

-- 正規表現で一致する文字列の一部を抽出する関数
CREATE FUNCTION dbo.CLR_Regex_FindMatches(
    @vTargetStr NVARCHAR(MAX)
	,@vTestPattern NVARCHAR(MAX)
	,@vIndex int
	,@vGroupIndex int
)
RETURNS NVARCHAR(MAX)
AS EXTERNAL NAME CLRRegexFunction.[nsRegexFunction.ClsCLRRegexFunction].FindMatchesRegex;
GO

-- 正規表現で一致する文字列の一部を抽出してテーブルにする関数
CREATE FUNCTION dbo.CLR_Regex_FindMatchesAsTable(
    @vTargetStr NVARCHAR(MAX)
	,@vTestPattern NVARCHAR(MAX)
)
RETURNS TABLE(
	MatchIndex int
	,GroupIndex int
	,CaptureIndex int
	,MatchOffset int
	,GroupOffset int
	,CaptureOffset int
	,TargetWord nvarchar(max)
)
AS EXTERNAL NAME CLRRegexFunction.[nsRegexFunction.ClsCLRRegexFunction].FindMatchesRegexAsTable;
GO

テスト実行

次のスクリプトで実行してみます。

.sql
declare @teststr nvarchar(max);
set @teststr='日本語　file=\abc\def\ghi.jkl (1234)';

select @teststr as [変換元文字列];

select dbo.CLR_Regex_Test(@teststr, '\d+') as [Regex_Testによる変換];
select dbo.CLR_Regex_Replace(@teststr, '\.\w+', '(replaced)') as [Regex_Replaceによる変換];
select dbo.CLR_Regex_Find(@teststr, '\\\w+') as [Regex_Findによる変換];
select dbo.CLR_Regex_FindMatches(@teststr, '\\(\w?)(\w*)', 3, 2) as [Regex_FindMatchesによる変換];
select * from dbo.CLR_Regex_FindMatchesAsTable(@teststr,'\\(\w?)(\w*)')

結果は次のとおりです。

Regex_Testによる変換
1

Regex_Replaceによる変換
日本語　file=\abc\def\ghi(replaced) (1234)

Regex_Findによる変換
\abc

Regex_FindMatchesによる変換
hi

MatchIndex	GroupIndex	CaptureIndex	MatchOffset	GroupOffset	CaptureOffset	TargetWord
1	1	1	10	10	10	\abc
1	2	1	10	11	11	a
1	3	1	10	12	12	bc
2	1	1	14	14	14	\def
2	2	1	14	15	15	d
2	3	1	14	16	16	ef
3	1	1	18	18	18	\ghi
3	2	1	18	19	19	g
3	3	1	18	20	20	hi

おわりに

CLRで正規表現関数を作成してみました。
ただし、細かなところを記載していないので各自で試す際に引っかかるところがあるかもしれません。
もし、より詳しく手順を記載してほしいなどの希望があればコメントしてください。

FindMatchesRegexAsTable2の追加（2025/04/13追記）

FindMatchesRegexAsTableを実際に使ってみると、グループを個別に指定するのは面倒くさいケースがありました。
なので、FindMatchesとFindMatchesRegexAsTableの中間的な結果を返す関数FindMatchesRegexAsTable2を作成してみました。


    ''' <summary>
    ''' FindMatchesRegexAsTable2で使うクラス
    ''' </summary>
    Private Class ElementOfFindMatches2
        Private ReadOnly _MatchIndex As SqlInt32
        Private ReadOnly _MatchOffset As SqlInt32
        Private ReadOnly _TargetWord0 As SqlString
        Private ReadOnly _TargetWord1 As SqlString
        Private ReadOnly _TargetWord2 As SqlString
        ... (省略) ...
        Private ReadOnly _TargetWord18 As SqlString
        Private ReadOnly _TargetWord19 As SqlString
        Private ReadOnly _TargetWord20 As SqlString

        Public ReadOnly Property MatchIndex() As SqlInt32
            Get
                Return _MatchIndex
            End Get
        End Property
        Public ReadOnly Property MatchOffset As SqlInt32
            Get
                Return _MatchOffset
            End Get
        End Property
        Public ReadOnly Property TargetWord0() As SqlString
            Get
                Return _TargetWord0
            End Get
        End Property
        Public ReadOnly Property TargetWord1() As SqlString
            Get
                Return _TargetWord1
            End Get
        End Property
        Public ReadOnly Property TargetWord2() As SqlString
            Get
                Return _TargetWord2
            End Get
        End Property
        ... (省略) ...
        Public ReadOnly Property TargetWord18() As SqlString
            Get
                Return _TargetWord18
            End Get
        End Property
        Public ReadOnly Property TargetWord19() As SqlString
            Get
                Return _TargetWord19
            End Get
        End Property
        Public ReadOnly Property TargetWord20() As SqlString
            Get
                Return _TargetWord20
            End Get
        End Property

        Public Sub New(
                      MatchIndex As SqlInt32,
                      MatchOffset As SqlInt32,
                      TargetWord0 As SqlString,
                      TargetWord1 As SqlString,
                      TargetWord2 As SqlString,
                      TargetWord3 As SqlString,
                      ... (省略) ...
                      TargetWord18 As SqlString,
                      TargetWord19 As SqlString,
                      TargetWord20 As SqlString
                      )
            _MatchIndex = MatchIndex
            _MatchOffset = MatchOffset
            _TargetWord0 = TargetWord0
            _TargetWord1 = TargetWord1
            _TargetWord2 = TargetWord2
            _TargetWord3 = TargetWord3
            ... (省略) ...
            _TargetWord18 = TargetWord18
            _TargetWord19 = TargetWord19
            _TargetWord20 = TargetWord20
        End Sub
    End Class

    ''' <summary>
    ''' ターゲット文字列のうちテストパターンと一致する文字列の一部を抽出してテーブルに展開する関数その２
    ''' </summary>
    ''' <param name="vTargetStr">対象の文字列</param>
    ''' <param name="vTestPattern">テストパターン</param>
    ''' <returns>抽出した文字列</returns>
    <SqlFunction(
        DataAccess:=DataAccessKind.None,
        IsDeterministic:=True,
        IsPrecise:=False,
        SystemDataAccess:=SystemDataAccessKind.None,
        Name:="FindMatchesRegexAsTable2",
        FillRowMethodName:="FindMatchesRegexAsTable2_FillRow",
        TableDefinition:="MatchIndex int,MatchOffset int,TargetWord0 nvarchar(max),TargetWord1 nvarchar(max),TargetWord2
... (省略) ...
nvarchar(max),TargetWord17 nvarchar(max),TargetWord18 nvarchar(max),TargetWord19 nvarchar(max),TargetWord20 nvarchar(max)"
    )>
    Public Shared Function FindMatchesRegexAsTable2(ByVal vTargetStr As SqlString, ByVal vTestPattern As SqlString) As IEnumerable
        Dim ans As ArrayList
        If vTargetStr.IsNull Or vTestPattern.IsNull Then
            ans = New ArrayList '空の集合
        Else
            ans = New ArrayList
            Dim m As Text.RegularExpressions.MatchCollection = Text.RegularExpressions.Regex.Matches(vTargetStr.Value, vTestPattern.Value)
            For mi As Integer = 0 To m.Count - 1
                Dim w(0 To 20) As String
                w = {"", "", "", "", "", "", "", "", "", "",
                    "", "", "", "", "", "", "", "", "", "", ""}
                Dim max As Integer = 21
                If max > m(mi).Groups.Count Then
                    max = m(mi).Groups.Count
                End If
                For gi As Integer = 0 To max - 1
                    With m(mi).Groups(gi)
                        If .Success Then
                            w(gi) = .Value
                        End If
                    End With
                Next
                ans.Add(
                    New ElementOfFindMatches2(
                        MatchIndex:=mi + 1,
                        MatchOffset:=m(mi).Index + 1,
                        TargetWord0:=w(0),
                        TargetWord1:=w(1),
                        TargetWord2:=w(2),
                        TargetWord3:=w(3),
                        ... (省略) ...
                        TargetWord18:=w(18),
                        TargetWord19:=w(19),
                        TargetWord20:=w(20)
                        ))
            Next
        End If
        Return ans
    End Function

    Public Shared Sub FindMatchesRegexAsTable2_FillRow(obj As Object,
            ByRef MatchIndex As SqlInt32,
            ByRef MatchOffset As SqlInt32,
            ByRef TargetWord0 As SqlString,
            ByRef TargetWord1 As SqlString,
            ByRef TargetWord2 As SqlString,
            ByRef TargetWord3 As SqlString,
            ... (省略) ...
            ByRef TargetWord18 As SqlString,
            ByRef TargetWord19 As SqlString,
            ByRef TargetWord20 As SqlString)
        Dim elem As ElementOfFindMatches2 = CType(obj, ElementOfFindMatches2)
        MatchIndex = elem.MatchIndex
        MatchOffset = elem.MatchOffset
        TargetWord0 = elem.TargetWord0
        TargetWord1 = elem.TargetWord1
        TargetWord2 = elem.TargetWord2
        TargetWord3 = elem.TargetWord3
        ... (省略) ...
        TargetWord18 = elem.TargetWord18
        TargetWord19 = elem.TargetWord19
        TargetWord20 = elem.TargetWord20
    End Sub
.sql

-- 正規表現で一致する文字列の一部を抽出してテーブルにする関数
CREATE FUNCTION dbo.CLR_Regex_FindMatchesAsTable2(
    @vTargetStr NVARCHAR(MAX)
	,@vTestPattern NVARCHAR(MAX)
)
RETURNS TABLE(
	MatchIndex int
	,MatchOffset int
	,TargetWord0 nvarchar(max)
	,TargetWord1 nvarchar(max)
	,TargetWord2 nvarchar(max)
	,TargetWord3 nvarchar(max)
	,TargetWord4 nvarchar(max)
	,TargetWord5 nvarchar(max)
	,TargetWord6 nvarchar(max)
	,TargetWord7 nvarchar(max)
	,TargetWord8 nvarchar(max)
	,TargetWord9 nvarchar(max)
	,TargetWord10 nvarchar(max)
	,TargetWord11 nvarchar(max)
	,TargetWord12 nvarchar(max)
	,TargetWord13 nvarchar(max)
	,TargetWord14 nvarchar(max)
	,TargetWord15 nvarchar(max)
	,TargetWord16 nvarchar(max)
	,TargetWord17 nvarchar(max)
	,TargetWord18 nvarchar(max)
	,TargetWord19 nvarchar(max)
	,TargetWord20 nvarchar(max)
)
AS EXTERNAL NAME CLRRegexFunction.[nsRegexFunction.ClsCLRRegexFunction].FindMatchesRegexAsTable2;
GO

関数を実行した結果は次のようになります。

.sql
declare @teststr nvarchar(max);
set @teststr='日本語　file=\abc\def\ghi.jkl (1234)';

select @teststr as [変換元文字列];

select dbo.CLR_Regex_FindMatches(@teststr, '\\(\w?)(\w*)', 3, 2) as [Regex_FindMatchesによる変換];
select * from dbo.CLR_Regex_FindMatchesAsTable(@teststr,'\\(\w?)(\w*)');
select * from dbo.CLR_Regex_FindMatchesAsTable2(@teststr,'\\(\w?)(\w*)');


変換元文字列
日本語　file=\abc\def\ghi.jkl (1234)

MatchIndex	GroupIndex	CaptureIndex	MatchOffset	GroupOffset	CaptureOffset	TargetWord
1	1	1	10	10	10	\abc
1	2	1	10	11	11	a
1	3	1	10	12	12	bc
2	1	1	14	14	14	\def
2	2	1	14	15	15	d
2	3	1	14	16	16	ef
3	1	1	18	18	18	\ghi
3	2	1	18	19	19	g
3	3	1	18	20	20	hi

MatchIndex	MatchOffset	TargetWord0	TargetWord1	TargetWord2	TargetWord3	TargetWord4	TargetWord5	TargetWord6	TargetWord7	TargetWord8	TargetWord9	TargetWord10	TargetWord11	TargetWord12	TargetWord13	TargetWord14	TargetWord15	TargetWord16	TargetWord17	TargetWord18	TargetWord19	TargetWord20
1	10	\abc	a	bc																		
2	14	\def	d	ef																		
3	18	\ghi	g	hi

FindMatchesRegexAsTable2では、複数個所に合致した場合に行単位で出力され、括弧によるグループ分けは列名で識別できるので、Select文などで指定しやすい形になります。また、cross applyで適用すると（スカラー型関数よりも）分かりやすく表現できると思います。

0
2
1

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
0
2

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?