愚者の経験

「また今度」はほとんどこない

SQL ServerでSplit

C#の勉強も兼ねてSQLCLR関数も少し作っています。

結構使えそうなのが「Split関数」です。返り値をテーブル値関数にすれば


declare @IDs nvarchar(max)

set @IDs = ‘100,101,102,103’

select * from table1
where ID番号 in (select ArrayValue from dbo.Split(@IDs,’,’))


のようにin句の中にパラメータを利用することが可能になります。
パフォーマンスは悪いかもしれませんが「ユーザーにデータを任意に複数選択させる」といった場合に、今までSQL文の動的実行(「exec」や「sp_executesql」)が必要であったのに比べて
かなり書きやすいのではないかと思います。

参考URL:http://stackoverflow.com/questions/3403447/regex-split-implementation-for-sql-server

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Collections;

public partial class UserDefinedFunctions
{
    private struct IndexedValue
    {
        public int Index;
        public string Value;
        public IndexedValue(int index, string value)
        {
            Index = index;
            Value = value;
        }
    }
    [SqlFunction(FillRowMethodName = “FillRow”)]
    public static IEnumerable Split(SqlChars input, SqlString delimiter)
    {
        string[] separator = { delimiter.Value };
        string[] split = input.ToSqlString().Value.Split(separator,StringSplitOptions.None);
        IndexedValue[] SplitList=new IndexedValue[split.Length];

        for (int i = 0; i < split.Length; i++)
        {
            SplitList[i] = new IndexedValue(i + 1, split[i]);
        }

        return SplitList;
    }
    public static void FillRow(object obj, out SqlInt32 ArrayIndex, out SqlString ArrayValue)
    {
        IndexedValue v = (IndexedValue)obj;
        ArrayIndex = v.Index;
        ArrayValue = v.Value;
    }
};



C#の構造体は「struct」で書きます。
構造体名と同名のコンストラクタ(?)を書いて初期化できる、ということです。

この関数をSQLServerに登録するにはコマンドプロンプトで

C:\Windows\Microsoft.NET\Framework\v3.5\csc.exe /t:library C:\Split.cs


のような感じでcsファイルからdllを作成し、任意のデータベースで以下のSQLを実行します。

create assembly Split

from ‘c:\Split.dll’        –←作成したdllファイルのパス
with permission_set=safe
go

create function Split
(@input nvarchar(max),@delimiter nvarchar(255))
returns
table(ArrayIndex int,ArrayValue nvarchar(4000))
as external NAME Split.UserDefinedFunctions.Split

SQLCLRが無効になっている場合は先に有効にしてください。

sp_configure ‘clr enabled’,1
reconfigure

Splitで切るだけならばかなり簡単だったのですが、範囲指定などの利便性を考慮して
インデックスをつけようと思いましたがこれが結構わからなかったです。
SQLCLRの関数ではスコープ変数が使えない(?)ようでしたので…。

広告

SQL ServerでSplit」への1件のフィードバック

  1. ピンバック:[SQL]動的にin句を変化させるような時の代替案 | 愚者の経験

コメントを残す

以下に詳細を記入するか、アイコンをクリックしてログインしてください。

WordPress.com ロゴ

WordPress.com アカウントを使ってコメントしています。 ログアウト / 変更 )

Twitter 画像

Twitter アカウントを使ってコメントしています。 ログアウト / 変更 )

Facebook の写真

Facebook アカウントを使ってコメントしています。 ログアウト / 変更 )

Google+ フォト

Google+ アカウントを使ってコメントしています。 ログアウト / 変更 )

%s と連携中

%d人のブロガーが「いいね」をつけました。