如何有效地將包含多個資料段的單一列值拆分為資料庫中的單獨欄位？

將單一欄位值拆分為多個欄位

在資料庫中，常見的任務是將包含多個部分的單一欄位值拆分資訊分成單獨的欄位。本文介紹了一種實現此資料轉換的內聯方法。

問題陳述

我們有一個訂閱表，其中訂閱號儲存為單一值在一列中。訂閱號碼由多個由破折號和空格分隔的段組成。目標是將此值拆分為各個列，例如前綴、段 1、段 2 等。

範例

考慮以下範例訂閱數字：

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

解

這是完成分割的內聯查詢：

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

結果

查詢的輸出如下如下：

+---------+---------+---------+---------+---------+---------+---------+
| 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]」提取第二個段，依此類推。 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

結論

提供的內聯方法是一種從單一列值中提取多個段的通用解決方案。它可以輕鬆調整以滿足您的特定數據要求。

以上是如何有效地將包含多個資料段的單一列值拆分為資料庫中的單獨欄位？的詳細內容。更多資訊請關注PHP中文網其他相關文章！