Consider a database table containing a subscription number stored in a single column. Each number follows a specific format:
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**
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
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!