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:

break into two parts

Answers (3)