break into two parts

Question

now i have an output like this:
Mon,Fri,0-1; Mon,Fri,1-2; Tue,Fri,2-3;
i want to break it like
Mon:0-1,1-2;
Fri:0-1,1-2,2-3;
using sql query is it possible

Dharmraj Thakur · Answer

First create this function to work previous code...

CREATE FUNCTION [dbo].[Split]
(
@String NVARCHAR(4000),
@Delimiter NCHAR(1)
)
RETURNS TABLE
AS
RETURN
(
WITH Split(stpos,endpos)
AS(
SELECT 0 AS stpos, CHARINDEX(@Delimiter,@String) AS endpos
UNION ALL
SELECT endpos+1, CHARINDEX(@Delimiter,@String,endpos+1)
FROM Split
WHERE endpos > 0
)
SELECT 'Data' = SUBSTRING(@String,stpos,COALESCE(NULLIF(endpos,0),LEN(@String)+1)-stpos)
FROM Split
)
GO

Dharmraj Thakur · Answer

I manipulate this data as below.... based on that you can go ahead...

DECLARE @udtList TABLE(Data VARCHAR(max));
DECLARE @udtList2 TABLE(Days VARCHAR(max),Range varchar(max));
declare @abc as nvarchar(max);
set @abc = 'Mon,Fri,0-1;Mon,Fri,1-2;Tue,Fri,2-3';
INSERT INTO @udtList select Data from Split(@abc, ';');
INSERT INTO @udtList2
(Days, Range)
select SUBSTRING(Data, 0,len(Data)-3) as Days, SUBSTRING(Data, len(Data)-2,len(Data)) as Range from @udtList
select * from @udtList2

Pooja Chowdhury · Answer

i got a string value
Mon:0,1,1,2,Tue:2,3;7,8;8,9;Wed:5,6,12,13,Thu:Fri:0,1,1,2,2,3,3,4,4,5,5,6,6,7,7,8,8,9,9,10,10,11,11,12,12,13,13,14,14,15,15,16,16,17,17,18,18,19,19,20,20,21,21,22,22,23,23,23,Sat:8,9,Sun:
i want to show
Mon:0,1,2,Tue:2,3;7,8,9;Wed:5,6,12,13,Thu:Fri:0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,23,Sat:8,9,Sun: