Break SQL Server row that has decimal points in multiple columns

Question

I have the following table

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

And I would like to be split into columns as follows:

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

I have the code below, but it does not account for the decimal value.

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

In SQL Server 2016+ you can use string_split().

In SQL Server until 2016, using the CSV Splitter function, the Jeff Moden function, and conditional aggregation:

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

Demo version of rexter: http://rextester.com/WJVLB77682

returns:

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

link for dividing lines:

• Tally OH! Improved SQL 8K "CSV Splitter" Feature - Jeff Moden
• Line Separation: A Follow-Up - Aaron Bertrand
• - -
• string_split() SQL Server 2016: Follow-Up # 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

...

, cross apply,

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