将具有小数点的 SQL 服务器字符串拆分为多个列

Question

>我有下表

Col
=========================
1270.8/847.2/254.16/106.9

我想像这样分成几列：

Col1         Col2         Col3         Col4
============================================
1270.8       847.2        254.16       106.9

我有下面的代码，但它没有考虑小数。

Declare @Sample Table
(MachineName varchar(max))
Insert into @Sample
values      ('1270.8/847.2/254.16');
SELECT 
     Reverse(ParseName(Replace(Reverse(MachineName), '/', ''), 1)) As [M1]
   , Reverse(ParseName(Replace(Reverse(MachineName), '/', ''), 2)) As [M2]
   , Reverse(ParseName(Replace(Reverse(MachineName), '/', ''), 3)) As [M3]
FROM @Sample

Answer 1

> 在SQL Server 2016+中，您可以使用string_split()。

在 2016 年之前的 SQL Server 中，使用 Jeff Moden 的 CSV 拆分器表值函数和条件聚合：

declare @Sample Table (id int not null identity(1,1), MachineName varchar(max));
insert into @Sample values ('1270.8/847.2/254.16'),('1270.8/847.2/254.16/106.9');
select 
    t.id
  , m1 = max(case when s.ItemNumber = 1 then s.Item end)
  , m2 = max(case when s.ItemNumber = 2 then s.Item end)
  , m3 = max(case when s.ItemNumber = 3 then s.Item end)
  , m4 = max(case when s.ItemNumber = 4 then s.Item end)
from @Sample t
  cross apply dbo.delimitedsplit8K(MachineName,'/') s
group by id

Rextester 演示：http://rextester.com/WJVLB77682

返回：

+----+--------+-------+--------+-------+
| id |   m1   |  m2   |   m3   |  m4   |
+----+--------+-------+--------+-------+
|  1 | 1270.8 | 847.2 | 254.16 | NULL  |
|  2 | 1270.8 | 847.2 | 254.16 | 106.9 |
+----+--------+-------+--------+-------+

拆分字符串参考：

• 理货哦！改进的 SQL 8K"CSV 拆分器"函数 - Jeff Moden
• 拆分字符串：后续 - 亚伦·伯特兰
• 以正确的方式拆分字符串 - 或次佳方式 - Aaron Bertrand
• SQL Server 2016 中的string_split()：跟进 #1 - Aaron Bertrand

Answer 2

每个人都应该有一个很好的拆分/解析函数，如 SQLZim (+1( 所示，但另一种选择可能如下：

Declare @YourTable table (ID int,Col varchar(max))
Insert Into @YourTable values
(1,'1270.8/847.2/254.16/106.9')
Select A.ID
      ,B.*
 From  @YourTable A
 Cross Apply (
                Select Col1 = xDim.value('/x[1]','float')
                      ,Col2 = xDim.value('/x[2]','float')
                      ,Col3 = xDim.value('/x[3]','float')
                      ,Col4 = xDim.value('/x[4]','float')
                From  (Select Cast('<x>' + replace((Select replace(A.Col,'/','§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml) as xDim) as A 
             ) B

返回

ID  Col1    Col2    Col3    Col4
1   1270.8  847.2   254.16  106.9

编辑 - 如果2012+，只是为了超级安全

Select A.ID
      ,B.*
 From  @YourTable A
 Cross Apply (
                Select Col1 = try_convert(float,xDim.value('/x[1]','varchar(100)'))
                      ,Col2 = try_convert(float,xDim.value('/x[2]','varchar(100)'))
                      ,Col3 = try_convert(float,xDim.value('/x[3]','varchar(100)'))
                      ,Col4 = try_convert(float,xDim.value('/x[4]','varchar(100)'))
                From  (Select Cast('<x>' + replace((Select replace(A.Col,'/','§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml) as xDim) as A 
             ) B

Answer 3

如果您使用的是 SQL Server 2016，则可以使用 String_Split((

;with cte as (
select RowN = row_number() over(order by (SELECT NULL)), * from string_split('1270.8/847.2/254.16/106.9','/')
) select * from cte
pivot (max(value) for RowN in ([1],[2],[3],[4])) p

如果您使用的低于 SQL Server 2016 版本，则可能需要使用自定义拆分函数...编写自定义拆分函数的多种方法 一种更简单的方法是使用 xml 编写

CREATE Function dbo.udf_split( @str varchar(max), @delimiter as varchar(5) )
RETURNS @retTable Table 
( RowN int,
value varchar(max)
)
AS
BEGIN
    DECLARE @xml as xml
    SET @xml = cast(('<X>'+replace(@str,@delimiter ,'</X><X>')+'</X>') as xml)
    INSERT INTO @retTable
    SELECT RowN = Row_Number() over (order by (SELECT NULL)), N.value('.', 'varchar(MAX)') as value FROM @xml.nodes('X') as T(N)
RETURN
END
--Your query
;with cte as (
select * from udf_split('1270.8/847.2/254.16/106.9','/')
) select * from cte
pivot (max(value) for RowN in ([1],[2],[3],[4])) p

但我的解决方案与约翰的解决方案相似...刚才只看那个

如果您在表中使用 in 值，那么您可以使用交叉应用，如下所示

create table #t (v varchar(50), i int)
insert into #t (v, i) values ('1270.8/847.2/254.16/106.9',1)
,('847.222/254.33/106.44',2)
select * from #t t cross apply string_split(t.v, '/')

Answer 4

create table #t (v varchar(50), i int)
insert into #t (v, i) values ('1270.8/847.2/254.16/106.9',1)
,('847.222/254.33/106.44',2)
--Just to get all the values
select * from #t t cross apply string_split(t.v, '/')
--Inorder to get into same row -pivoting the data
select * from (
select * from #t t cross apply (select RowN=Row_Number() over (Order by (SELECT NULL)), value from string_split(t.v, '/') ) d) src
pivot (max(value) for src.RowN in([1],[2],[3],[4])) p