sql - 将具有小数点的 SQL Server 字符串拆分为多个列

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 Splitter 表值函数和条件聚合:

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

分割字符串引用:

• Tally OH! An Improved SQL 8K “CSV Splitter” Function - Jeff Moden
• Splitting Strings : A Follow-Up - Aaron Bertrand
• Split strings the right way – or the next best way - Aaron Bertrand
• string_split() in SQL Server 2016 : Follow-Up #1 - Aaron Bertrand