也许是这样的

Select A.[Name]
      ,C.*
 From  YourTable A
 Cross Apply ( values ( replace(replace([Name],'.',';'),':',';') ))B(CleanString)
 Cross Apply (
                Select Pos1 = ltrim(rtrim(xDim.value('/x[1]','varchar(100)')))  
                      ,Pos2 = ltrim(rtrim(xDim.value('/x[2]','varchar(100)')))
                      ,Pos3 = ltrim(rtrim(xDim.value('/x[3]','varchar(100)')))
                      ,Pos4 = ltrim(rtrim(xDim.value('/x[4]','varchar(100)')))
                      ,Pos5 = ltrim(rtrim(xDim.value('/x[5]','varchar(100)')))
                      ,Pos6 = ltrim(rtrim(xDim.value('/x[6]','varchar(100)')))
                From  (Select Cast('<x>' + replace(CleanString,';','</x><x>')+'</x>' as xml) as xDim) as A 
             ) C

退货

您还可以使用字符串方法执行直接查询，如

select substring(name, 1, charindex('.', name) - 1) as name1,
       substring(name, charindex('.', name) + 1, charindex(':', name) - charindex('.', name) -1) as name2,
       substring(name, charindex(':', name) + 1, charindex(';', name) -  charindex(':', name)  -1) as name3,
       substring(name, charindex(';', name) + 1, charindex(';', name,charindex(';', name) + 1) - charindex(';', name)-1) as name4,
       substring(name, charindex(';', name,charindex(';', name) + 1) + 1, charindex(';', name,charindex(';', name,charindex(';', name) + 1) + 1) - charindex(';', name,charindex(';', name) + 1) -1) as name5,
       substring(name, charindex(';', name,charindex(';', name,charindex(';', name) + 1) + 1) + 1, LEN(Name) - charindex(';', name,charindex(';', name,charindex(';', name) + 1) + 1) as name6
from YourTable

但后来还是变得很复杂。
如果可能的话，最好的选择是寻找一种不同的格式来存储数据。

如果您使用的是sql server 2016或更高版本，则可以使用string\u split以获得更好的性能。请尝试以下操作：

;WITH cte AS(
SELECT [Name]
      ,value 
      ,ROW_NUMBER() OVER(PARTITION BY [Name] ORDER BY (SELECT NULL)) as rn
FROM @t t
    CROSS APPLY STRING_SPLIT(replace(replace([Name], '.', ';'), ':', ';'), ';') AS ss
)
SELECT
       [1] AS Name1
      ,[2] AS Name2
      ,[3] AS Name3
      ,[4] AS Name4
      ,[5] AS Name5
      ,[6] AS Name6
FROM cte
PIVOT(
    MAX(VALUE)
    FOR RN IN([1],[2],[3],[4],[5],[6])
) as PVT

db<>在这里摆弄。
请在这里找到更多关于性能的信息。