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

Splitting a Single Column into Multiple Columns

Problem Statement

Consider a database table containing a subscription number stored in a single column. Each number follows a specific format:

• SC followed by one or more space-delimited values.

For instance:

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

The objective is to split this single-column data into multiple columns, resulting in a structured representation:

**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**

Solution

To achieve this, we can utilize the combination of XML and CROSS APPLY in an in-line query:

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

Alternative Solution

For a more complete implementation, this query can be integrated into the creation of a new table that stores the split values:

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

Executing this query creates the MyNewPubTable with the split values.

The above is the detailed content of How can I split a single database column containing space-delimited values into multiple columns using SQL?. For more information, please follow other related articles on the PHP Chinese website!