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

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

Patricia Arquette

リリース： 2024-12-17 10:41:24

オリジナル

508 人が閲覧しました

How can I efficiently split a single column value containing multiple data segments into separate columns in a database?

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

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

問題ステートメント

サブスクリプション番号が単一の値として保存されているサブスクリプションのテーブルがあります。 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 サイトの他の関連記事を参照してください。