SQL Server - Splitting a single, variable length column that is delimited with a / into multiple columns

Question

I'm trying to split this field into columns separating at the '/' character. The string contains the Company Name/Location/Cost Center/Department/Job

Here is a sample:

JSM MFG/Stearns Blg/Operations/Shipping/Packer

JSM MFG/Birch Lane Blg/Maintenance/Electrical/Electrician II

The desired output is:

JSM MFG, Stearns Blg, Operations, Shipping, Packer 

into their respective separate columns.

This would be combined with other columns, too, employee number, rate of pay etc.. which are just direct selects.

I have found a few different SQL excerpts but nothing that I could see that covers multiple delimiters with variable length in one string.

Thanks in advance, Doug

Answer 1

Here's a solution that maybe you can build on. It uses the SQL Server 2016+ string_split() table value function to split the string into fields, which are then mapped into columns on a table you want to update.

declare @empId int = 123456 -- Let's assume a table Employee you want to update.

-- You get the values you want to split from somewhere.
declare @example varchar(max) = 'JSM MFG/Stearns Blg/Operations/Shipping/Packer'

-- SQL Server 2016+ has a handy string_split() table function we can use.
-- We'll dump the split values into a temp table.
select value into #tmp from string_split(@example, '/')

-- We now retroactively add an identity column to the temp table,
-- so we can tell which positions the values map to.
alter table #tmp add i int identity(1,1)


-- And now, a cursor to iterate over the split values.

declare @i int
declare @value varchar(max)

declare split_cursor cursor for 
select i, value from #tmp

open split_cursor  
fetch next from split_cursor into @i, @value 

while @@fetch_status = 0  
begin  

    -- We know which values correspond to which positions in the Employee table,
    -- thanks to the identity integer we added to the temp table above.
    if (@i = 1)
    begin
        update Employee set Employer = @value where EmpId = @empId
    end
    else if (@i = 2)
    begin
        update Employee set Building = @value where EmpId = @empId
    end
    -- ETC...

    fetch next from split_cursor into @i, @value
end 

close split_cursor  
deallocate split_cursor

Answer 2

There's more than 1 way to do this.

Here's a demonstration of some:

create table Test (
 id int identity(1,1) primary key,
 col nvarchar(100)
);
GO

✓

insert into Test (col) values
('abc/def/ghi/jkl/mno'),
('s/t/u/f/f'),
('w/h/y'),
(null);
GO

4 rows affected

SELECT id, 
 s1 AS [CompanyName], 
 s2 AS [Location],
 s3 AS [Cost Center],
 s4 AS [Department],
 s5 AS [Job]
FROM Test t
OUTER APPLY
(
    SELECT s1, s2, s3, s4, s5
    FROM (VALUES (col+'/')) q(s0)
    CROSS APPLY (select case when charindex('/',s0)>0 then left(s0, charindex('/',s0)-1) end s1, case when charindex('/',s0)>0 then charindex('/',s0) end p1) a1
    CROSS APPLY (select case when p1>0 and charindex('/',s0,p1+1)>0 then substring(s0, p1+1, charindex('/',s0,p1+1)-p1-1) end s2, case when p1>0 then charindex('/',s0,p1+1) end p2) a2
    CROSS APPLY (select case when p2>0 and charindex('/',s0,p2+1)>0 then substring(s0, p2+1, charindex('/',s0,p2+1)-p2-1) end s3, case when p2>0 then charindex('/',s0,p2+1) end p3) a3
    CROSS APPLY (select case when p3>0 and charindex('/',s0,p3+1)>0 then substring(s0, p3+1, charindex('/',s0,p3+1)-p3-1) end s4, case when p3>0 then charindex('/',s0,p3+1) end p4) a4
    CROSS APPLY (select case when p4>0 and charindex('/',s0,p4+1)>0 then substring(s0, p4+1, charindex('/',s0,p4+1)-p4-1) end s5) a5
) a;
GO

id | CompanyName | Location | Cost Center | Department | Job 
-: | :---------- | :------- | :---------- | :--------- | :---
 1 | abc         | def      | ghi         | jkl        | mno 
 2 | s           | t        | u           | f          | f   
 3 | w           | h        | y           | null       | null
 4 | null        | null     | null        | null       | null

select id,
 [1] AS [CompanyName], 
 [2] AS [Location],
 [3] AS [Cost Center],
 [4] AS [Department],
 [5] AS [Job]
from Test t
outer apply (
   select *
   from
   (
     select value
     , row_number() over (order by (select 0)) n
     from string_split(t.col,'/') s
   ) src
   pivot (
     max(value) 
     for n in ([1],[2],[3],[4],[5])
  ) pvt
) a;
GO

id | CompanyName | Location | Cost Center | Department | Job 
-: | :---------- | :------- | :---------- | :--------- | :---
 1 | abc         | def      | ghi         | jkl        | mno 
 2 | s           | t        | u           | f          | f   
 3 | w           | h        | y           | null       | null
 4 | null        | null     | null        | null       | null

select id,
 s1 AS [CompanyName], 
 s2 AS [Location],
 s3 AS [Cost Center],
 s4 AS [Department],
 s5 AS [Job]
from Test t
outer apply (
   select 
     s1 = x0.value('/x[1]','nvarchar(max)')
   , s2 = x0.value('/x[2]','nvarchar(max)')
   , s3 = x0.value('/x[3]','nvarchar(max)')
   , s4 = x0.value('/x[4]','nvarchar(max)')
   , s5 = x0.value('/x[5]','nvarchar(max)')
   from 
   (
       select 
       cast(('<x>'+ replace(col,'/','</x><x>') +'</x>') as xml) x0
   ) q
) a;
GO

id | CompanyName | Location | Cost Center | Department | Job 
-: | :---------- | :------- | :---------- | :--------- | :---
 1 | abc         | def      | ghi         | jkl        | mno 
 2 | s           | t        | u           | f          | f   
 3 | w           | h        | y           | null       | null
 4 | null        | null     | null        | null       | null

db<>fiddle here