Trim the data and put in separate columns

Question

I have a below records in the table with single column .

         **Name**
Aaa.bbb:ccc;ddd;eee;fff
Www.xxx:yyy;zzz;rrr;hhh

I am looking for a output something like this :

Name1 name2 name3 name4 name5 name6
Aaa   bbb    ccc   ddd   eee   fff
Www   xxx    yyy   zzz   rrr   hhh

Please help with a select query to accomplish this .

Thanks.

John Cappelletti · Answer 1 · 2020-07-24T21:31:21.567

Perhaps something like this

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

Returns

score 0 · Answer 2 · answered Jul 24 '20 at 22:08

You could also do a straight query with string methods like

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

but then it still gets very complex.

The best option is to look for a different format to store your data if that is possible.

score 0 · Answer 3 · answered Jul 25 '20 at 00:19

If you are using SQL Server 2016 or higher, you can use string_split for better performance. Try the following:

;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<>fiddle here.

Please find more on performance here.

Trim the data and put in separate columns

3 Answers3