如何有效地将包含多个数据段的单个列值拆分为数据库中的单独列？

将单个列值拆分为多个列

在数据库中，常见的任务是将包含多个部分的单个列值拆分信息分成单独的列。本文介绍了一种实现此数据转换的内联方法。

问题陈述

我们有一个订阅表，其中订阅号存储为单个值在一列中。订阅号由多个由破折号和空格分隔的段组成。目标是将此值拆分为各个列，例如前缀、段 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中文网其他相关文章！