如何使用 SQL 將包含空格分隔值的單一資料庫列拆分為多個欄位？-mysql教程-PHP中文網

如何使用 SQL 將包含空格分隔值的單一資料庫列拆分為多個欄位？

Barbara Streisand

發布： 2024-12-24 15:10:16

原創

763 人瀏覽過

How can I split a single database column containing space-delimited values into multiple columns using SQL?

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

問題陳述

考慮一個資料庫表，其中包含儲存在單一欄位中的訂閱號。每個數字都遵循特定的格式：

SC 後面跟著一個或多個空格分隔的值。

例如：

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

登入後複製

目標是將單列資料拆分為多列，從而產生結構化的表示：

**Col1**   **Col2**   **Col3**   **Col4**  **Col5**  **Col6**   **Col7**
**SC**      **5**     **1395**   **174**   **25P** 
**SC**      **1**     **2134**   **123**   **ABC**   **C1**      **2**
**SC**      **12**    **5245**   **1247**  **14&P**
**SC**      **ABCD**  **2525**   **120**

登入後複製

解決方案

為了實現這一點，我們可以在內聯查詢中結合使用XML 和CROSS APPLY：

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

登入後複製

替代解決方案

為了更完整的實現，可以將此查詢整合到儲存分割的新表的建立中位數：

CREATE TABLE MyNewPubTable (PUB_FORM_NUM NVARCHAR(50) , COL1 NVARCHAR(10) , COL2 NVARCHAR(10) , COL3 NVARCHAR(10) , COL4 NVARCHAR(10) , COL5 NVARCHAR(10) , COL6 NVARCHAR(10) , COL7 NVARCHAR(10)) 

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')

Insert Into MyNewPubTable
Select A.PUB_FORM_NUM
      ,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.PUB_FORM_NUM,' ','-'),'-','§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml) as xDim) as A 
             ) B

登入後複製

執行此查詢將建立具有分割值的MyNewPubTable。

以上是如何使用 SQL 將包含空格分隔值的單一資料庫列拆分為多個欄位？的詳細內容。更多資訊請關注PHP中文網其他相關文章！