複数のデータ セグメントを含む 1 つの列の値をデータベース内の別々の列に効率的に分割するにはどうすればよいですか?

単一の列の値を複数の列に分割する

データベースでは、一般的なタスクは、複数の部分を含む単一の列の値を分割することです。情報を別の列に分割します。この記事では、このデータ変換を実現するためのインライン アプローチを紹介します。

問題ステートメント

サブスクリプション番号が単一の値として保存されているサブスクリプションのテーブルがあります。 1つの列で。サブスクリプション番号は、ダッシュとスペースで区切られた複数のセグメントで構成されます。目標は、この値をプレフィックス、セグメント 1、セグメント 2 などの個別の列に分割することです。

例

次のサンプル サブスクリプションについて考えてみましょう。数値:

SC 5-1395-174-25P
SC 1-2134-123-ABC C1-2
SC 12-5245-1247-14&P
SC ABCD-2525-120

Solution

分割を実行するインライン クエリは次のとおりです:

Declare @YourTable table (SomeCol varchar(max))
Insert Into @YourTable values
('SC 5-1395-174-25P'),
('SC 1-2134-123-ABC C1-2'),
('SC 12-5245-1247-14&P'),
('SC ABCD-2525-120')


Select B.*
 From  @YourTable A
 Cross Apply (
                Select Pos1 = ltrim(rtrim(xDim.value('/x[1]','varchar(max)')))
                      ,Pos2 = ltrim(rtrim(xDim.value('/x[2]','varchar(max)')))
                      ,Pos3 = ltrim(rtrim(xDim.value('/x[3]','varchar(max)')))
                      ,Pos4 = ltrim(rtrim(xDim.value('/x[4]','varchar(max)')))
                      ,Pos5 = ltrim(rtrim(xDim.value('/x[5]','varchar(max)')))
                      ,Pos6 = ltrim(rtrim(xDim.value('/x[6]','varchar(max)')))
                      ,Pos7 = ltrim(rtrim(xDim.value('/x[7]','varchar(max)')))
                From  (Select Cast('<x>' + replace((Select replace(replace(A.SomeCol,' ','-'),'-','§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml) as xDim) as A 
             ) B

Result

クエリの出力は次のとおりです

+---------+---------+---------+---------+---------+---------+---------+
| COL1    | COL2    | COL3    | COL4    | COL5    | COL6    | COL7    |
+---------+---------+---------+---------+---------+---------+---------+
| SC      | 5       | 1395    | 174     | 25P     | NULL    | NULL    |
| SC      | 1       | 2134    | 123     | ABC     | C1      | 2       |
| SC      | 12      | 5245    | 1247    | 14&P    | NULL    | NULL    |
| SC      | ABCD    | 2525    | 120     | NULL    | NULL    | NULL    |
+---------+---------+---------+---------+---------+---------+---------+

説明

クエリは、クロス適用を使用して、サブスクリプション番号のセグメントごとに行のセットを作成します。 XML パス式 '/x[1]' は最初のセグメントを抽出し、'/x[2]' は 2 番目のセグメントを抽出します。 ltrim 関数と rtrim 関数は、先頭または末尾の空白をクリーンアップするために使用されます。

代替アプローチ

分割を保存する新しいテーブルをその場で作成することもできます。値:

Declare @YourTable table (PUB_FORM_NUM varchar(max))
Insert Into @YourTable values
('SC 5-1395-174-25P'),
('SC 1-2134-123-ABC C1-2'),
('SC 12-5245-1247-14&P'),
('SC ABCD-2525-120')

Select A.PUB_FORM_NUM
      ,B.*
 Into  MyNewPubTable
 From  @YourTable A
 Cross Apply (
                Select Pos1 = ltrim(rtrim(xDim.value('/x[1]','varchar(max)')))
                      ,Pos2 = ltrim(rtrim(xDim.value('/x[2]','varchar(max)')))
                      ,Pos3 = ltrim(rtrim(xDim.value('/x[3]','varchar(max)')))
                      ,Pos4 = ltrim(rtrim(xDim.value('/x[4]','varchar(max)')))
                      ,Pos5 = ltrim(rtrim(xDim.value('/x[5]','varchar(max)')))
                      ,Pos6 = ltrim(rtrim(xDim.value('/x[6]','varchar(max)')))
                      ,Pos7 = ltrim(rtrim(xDim.value('/x[7]','varchar(max)')))
                From  (Select Cast('<x>' + replace((Select replace(replace(A.PUB_FORM_NUM,' ','-'),'-','§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml) as xDim) as A 
             ) B

Select * From MyNewPubTable

結論

提供されるインライン アプローチは、単一の列値から複数のセグメントを抽出するための多用途のソリューションです。特定のデータ要件を満たすように簡単に調整できます。

以上が複数のデータ セグメントを含む 1 つの列の値をデータベース内の別々の列に効率的に分割するにはどうすればよいですか?の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。