How to split two columns delimited by '/' into multiple rows.

Question

Hi,

I want to split two columns delimited by '/' into multiple rows. I don't know how to do it. I am using SQL Server Express 2012.

What I have
Sl Locations Ratings
132 ABC/DEF/GHE L/M/H
332 ABC/GHE M/H

Result I want:
Sl Locations Location Rating
132 ABC/DEF/GHE ABC L
132 ABC/DEF/GHE DEF M
132 ABC/DEF/GHE GHE H
332 ABC/GHE ABC M
332 ABC/GHE GHE H

Best regards

Sam

Answer 1

This is much simpler.

CREATE FUNCTION [dbo].[split](
          @delimited NVARCHAR(MAX),
          @delimiter NVARCHAR(100)
        ) RETURNS @t TABLE (id INT IDENTITY(1,1), val NVARCHAR(MAX))
        AS
        BEGIN
          DECLARE @xml XML
          SET @xml = N'<t>' + REPLACE(@delimited,@delimiter,'</t><t>') + '</t>'

          INSERT INTO @t(val)
          SELECT  r.value('.','nvarchar(MAX)') as item
          FROM  @xml.nodes('/t') as records(r)
          RETURN
        END
GO


 DECLARE @tbl TABLE (Sl INT, Locations VARCHAR(100), Ratings VARCHAR(100));
 INSERT INTO @tbl VALUES
 (132 ,'ABC/DEF/GHE', 'L/M/H'),
 (332, 'ABC/GHE', 'M/H');

SELECT t.Sl, t.Locations, l.val as [Location], r.val as [Rating]
FROM @tbl t
 CROSS APPLY dbo.split(t.Locations,'/') l
 CROSS APPLY dbo.split(t.Ratings,'/') r
WHERE l.id=r.id

Answer 2

Hi @Sam ,

SQLServer before 2016 cannot use string_split, your SQLServer version is 2012, so you need to use user-defined functions to achieve.

Please refer to:

create table #test(Sl int, Locations char(15), Ratings char(15))  
insert into #test values(132 ,'ABC/DEF/GHE', 'L/M/H'),(332, 'ABC/GHE', 'M/H')  
  
CREATE FUNCTION SplitStr(@Sourcestr VARCHAR(8000), @Seprate VARCHAR(100))       
RETURNS @result TABLE(F1 VARCHAR(100))       
  AS         
   BEGIN       
   DECLARE @sql AS VARCHAR(100)       
  SET @Sourcestr=@Sourcestr+@Seprate         
  WHILE(@Sourcestr<>'')       
  BEGIN       
    SET @sql=left(@Sourcestr,CHARINDEX('/',@Sourcestr,1)-1)       
    INSERT @result VALUES(@sql)       
     SET @Sourcestr=STUFF(@Sourcestr,1,CHARINDEX('/',@Sourcestr,1),'')       
   END       
   RETURN    
   END  
 GO  
  
;with cte  
as(SELECT *,row_number() over(partition by Locations order by Locations) r1  
FROM #test t   
CROSS APPLY SplitStr(t.Locations,'/') V)  
,cte2 as(SELECT *, row_number() over(partition by Locations order by Locations) r2  
FROM #test t  
CROSS APPLY SplitStr(t.Ratings,'/') V)  
  
select c1.Sl, c1.Locations,c1.Ratings,c1.F1,c2.F1 from cte c1  
join cte2 c2 on c1.Sl=c2.Sl and c1.r1=c2.r2   
  
drop table #test  
drop function SplitStr  

Output:

Sl Locations Ratings F1 F1  
132 ABC/DEF/GHE     L/M/H           ABC L  
132 ABC/DEF/GHE     L/M/H           DEF M  
132 ABC/DEF/GHE     L/M/H           GHE H            
332 ABC/GHE         M/H             ABC M  
332 ABC/GHE         M/H             GHE H     

Regards
Echo

If you have any question, please feel free to let me know.
If the response is helpful, please click "Accept Answer" and upvote it.

Regards
Echo

---

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Hot issues November--What can I do if my transaction log is full?
Hot issues November--How to convert Profiler trace into a SQL Server table

Answer 3

Please try the following method.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (Sl INT, Locations VARCHAR(100), Ratings VARCHAR(100));
INSERT INTO @tbl VALUES
(132 ,'ABC/DEF/GHE', 'L/M/H'),
(332, 'ABC/GHE', 'M/H');
-- DDL and sample data population, end

DECLARE @separator CHAR(1) = '/';

;WITH rs AS
(
    SELECT Sl, Locations
 , CAST('<root><r>' + 
 REPLACE(CAST(Locations AS NVARCHAR(MAX)), @separator, '</r><r>') + '</r></root>' AS XML) AS loc_xml
 , CAST('<root><r>' + 
 REPLACE(CAST(Ratings AS NVARCHAR(MAX)), @separator, '</r><r>') + '</r></root>' AS XML) AS rat_xml
    FROM @tbl
), t1 AS 
(
 SELECT Sl, Locations
    , c.value('(./text())[1]','VARCHAR(100)') AS [Location]
    , ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS seq
 FROM rs
    CROSS APPLY loc_xml.nodes('/root/r') t(c)
)
, t2 AS
(
 SELECT Sl
    , c.value('(./text())[1]','VARCHAR(100)') AS [Rating]
    , ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS seq
 FROM rs
    CROSS APPLY rat_xml.nodes('/root/r') t(c)
)
SELECT t1.Sl, t1.Locations
 , t1.[Location]
 , t2.[Rating]
FROM t1 INNER JOIN t2 ON t1.Sl = t2.Sl
 AND t1.seq = t2.seq;

Note
After being a one full year in operation since its launch, this forum still doesn't allow to use the CDATA sections in the source code.