文字列を連結するためのSQLServer関数
列または変数のセットからフォーマットされ、区切られたテキスト文字列を返す、柔軟で再利用可能なSQLServer関数
適切にフォーマットされたテキストの表示
この関数は、適切にフォーマットされた文字列でアドレスを表示する必要性から生まれました。この種のデータを操作する場合、データフィールド内に空白、NULL値、場合によっては区切られたテキストが含まれることがよくあります。列の標準的な連結により、末尾に空白またはコンマが付いた二重区切り文字が使用されました。
これを念頭に置いて、空白を除外し、空白をトリミングし、末尾のデータを削除して、データを分離する方法が必要でした。標準のCOALESCEを単独で使用する方法はいくつかありますが、それでは空白でうまくいきませんでした。
試行錯誤したTextToRows関数を使用してテキストを分割および並べ替えてから、テキスト値を新しい文字列に書き戻しました。これの利点は、入力と出力の両方に選択した区切り文字を使用してデータを再利用できることです。
SQL
ALTER FUNCTION dbo.TextConc(@Text NVARCHAR(MAX),@Delim CHAR(1),@Sep CHAR(2)) RETURNS NVARCHAR(MAX) WITH SCHEMABINDING AS BEGINDECLARE @Str NVARCHAR(MAX)SELECT @Str=COALESCE(@Str+@Sep,'')+REPLACE(WordStr,@Delim,'')FROM dbo.TextToRows(@Delim,@Text)WHERE WordStr<>''RETURN REPLACE(@Str,@Sep+@Sep,@Sep)ENDGOテストデータ
テストの目的で、一時テーブルを作成し、いくつかのアドレスを格納できます。 selectステートメントを関数と組み合わせると、表示目的に最適な、鮮明にフォーマットされたデータセットがどのように返されるかを確認できます。
SQL
DECLARE @Address TABLE(AddressName NVARCHAR(100),AddressL1 NVARCHAR(100),AddressL2 NVARCHAR(100),AddressL3 NVARCHAR(100),AddressCity NVARCHAR(100),AddressCounty NVARCHAR(100),AddressCountry NVARCHAR(100),AddressPostCode NVARCHAR(100))INSERT INTO @AddressSELECT 'Mountain View','1600 Amphitheatre Parkway',NULL,NULL,'Mountain View','California','United States','94043'INSERT INTO @AddressSELECT 'London','1-13 St Giles High St',NULL,NULL,'London','London','United Kingdom','WC2H 8LG'SELECT dbo.TextConc(ISNULL(AddressName,'')+','+ISNULL(AddressL1,'')+','+ISNULL(AddressL2,'')+','+ISNULL(AddressL3,'')+','+ISNULL(AddressCity,'')+','+ISNULL(AddressCounty,'')+','+ISNULL(AddressCountry,'')+','+ISNULL(AddressPostCode,''),',',', ')FROM @AddressResults
Mountain View, 1600 Amphitheatre Parkway, Mountain View, California, United States, 94043
London, 1-13 St Giles High St, London, London, United Kingdom, WC2H 8LG
テーブル内のデータを永続化する
SCHEMABINDINGを使用してデータを作成したので、それを計算列としてテーブルに追加することもできます。
これらを追加すると、クエリのパフォーマンスに影響を与える可能性があるため、通常はデータを永続化するように努めます。これにより、実際にデータがテーブルに保存され、挿入/更新トランザクションでのみ計算されます。
SQL
CREATE TABLE TestAddress(AddressName NVARCHAR(100),AddressL1 NVARCHAR(100),AddressL2 NVARCHAR(100),AddressL3 NVARCHAR(100),AddressCity NVARCHAR(100),AddressCounty NVARCHAR(100),AddressCountry NVARCHAR(100),AddressPostCode NVARCHAR(100),AddressDisplay as dbo.TextConc(ISNULL(AddressName,'')+','+ISNULL(AddressL1,'')+','+ISNULL(AddressL2,'')+','+ISNULL(AddressL3,'')+','+ISNULL(AddressCity,'')+','+ISNULL(AddressCounty,'')+','+ISNULL(AddressCountry,'')+','+ISNULL(AddressPostCode,''),',',', ') PERSISTED)INSERT INTO TestAddressSELECT 'Mountain View','1600 Amphitheatre Parkway',NULL,NULL,'Mountain View','California','United States','94043'INSERT INTO TestAddressSELECT 'London','1-13 St Giles High St',NULL,NULL,'London','London','United Kingdom','WC2H 8LG'SELECT * FROM TestAddressDROP TABLE TestAddress