How to loop thru this array [[09:00 AM-11:00 AM],[02:00PM-03:00PM]

Question

I have a table called Employee which has 2 column say FromDate and ToDate, both are of type nvarchar(max). Now I am getting input like this [[09:00 AM-11:00 AM],[02:00PM-03:00PM]. I have to insert each value in the fromdate and todate like below

Fromdate    ToDate
09:00AM      11:00 AM
02:00PM       03:00PM

How to do in an SQL procedure or statement?

Answer 1

If on SQL Server 2016 or higher you can accomplish this with the string_split function.

create table #tmp(input varchar(max))

insert into #tmp
values ('[09:00 AM-11:00 AM],[02:00PM-03:00PM]')

select value as part
into #tmp2
from #tmp
cross apply string_split(input,',')

SELECT part,
      replace([1],'[','') AS [from],
      replace([2],']','') AS [to]
FROM(

    SELECT sp.part,ss.value,
          ROW_NUMBER() OVER(partition by part ORDER BY part) AS [rn]
    FROM #tmp2 AS sp
    CROSS APPLY string_split(part, '-') AS ss
    ) AS [a] 
    PIVOT(MAX(value) FOR [rn] IN([1],[2])) AS [pvt];

Here is some Additional info about apply and pivot.

Answer 2

It could be done this way too, without a temporary table.

Idea thanks to @Bob Klimes

This is if it is not SQL Server 2016

--SIMULATED TEXT
DECLARE @Text NVARCHAR(MAX)='[09:00 AM-11:00 AM],[02:00PM-03:00PM]'

-- CTE TO GET THE VALUES DIVIDE by commas
    ;WITH CTE AS (
    SELECT ROW_NUMBER() OVER(ORDER BY(SELECT NULL)) AS N, A.[value] as part 
    FROM dbo.fn_Split(@Text,',') A
    )
--SELECT THE DATA
    SELECT part,
      REPLACE([1],'[','') AS [from],
      REPLACE([2],']','') AS [to]
FROM(
    SELECT part,ss.value,
          ROW_NUMBER() OVER(partition by part ORDER BY part) AS [rn]
    FROM CTE 
    CROSS APPLY dbo.fn_Split(part, '-') AS ss
    ) AS [a] 
    PIVOT(MAX(value) FOR [rn] IN([1],[2])) AS [pvt];

if you don't have the function here with this statement you can create it

--=================*===================  
--Create date :---  
--By :---  
-- Description:<FUNCINO SPLIT Generic>  
--==================================== | Version------  
CREATE FUNCTION [dbo].[fn_Split](@sText varchar(max), @sDelim varchar(20) = ' ')  
RETURNS @retArray TABLE (idx smallint Primary Key, value varchar(8000))  
AS  
BEGIN  
DECLARE @idx smallint,  
 @value varchar(max),  
 @bcontinue bit,  
 @iStrike smallint,  
 @iDelimlength tinyint  
IF @sDelim = 'Space'  
 BEGIN  
 SET @sDelim = ' '  
 END  
SET @idx = 0  
SET @sText = LTrim(RTrim(@sText))  
SET @iDelimlength = DATALENGTH(@sDelim)  
SET @bcontinue = 1  
IF NOT ((@iDelimlength = 0) or (@sDelim = 'Empty'))  
 BEGIN  
 WHILE @bcontinue = 1  
  BEGIN  
--If you can find the delimiter in the text, retrieve the first element and  
--insert it with its index into the return table.  

  IF CHARINDEX(@sDelim, @sText)>0  
   BEGIN  
   SET @value = SUBSTRING(@sText,1, CHARINDEX(@sDelim,@sText)-1)  
    BEGIN  
    INSERT @retArray (idx, value)  
    VALUES (@idx, @value)  
    END  

--Trim the element and its delimiter from the front of the string.  
   --Increment the index and loop.  
   SET @iStrike = DATALENGTH(@value) + @iDelimlength  
    SET @idx = @idx + 1  
    SET @sText = LTrim(Right(@sText,DATALENGTH(@sText) - @iStrike))  

   END  
  ELSE  
   BEGIN  
--If you can't find the delimiter in the text, @sText is the last value in  
--@retArray.  
 SET @value = @sText  
    BEGIN  
    INSERT @retArray (idx, value)  
    VALUES (@idx, @value)  
    END  
   --Exit the WHILE loop.  
SET @bcontinue = 0  
   END  
  END  
 END  
ELSE  
 BEGIN  
 WHILE @bcontinue=1  
  BEGIN  
  --If the delimiter is an empty string, check for remaining text  
  --instead of a delimiter. Insert the first character into the  
  --retArray table. Trim the character from the front of the string.  
--Increment the index and loop.  
  IF DATALENGTH(@sText)>1  
   BEGIN  
   SET @value = SUBSTRING(@sText,1,1)  
    BEGIN  
    INSERT @retArray (idx, value)  
    VALUES (@idx, @value)  
    END  
   SET @idx = @idx+1  
   SET @sText = SUBSTRING(@sText,2,DATALENGTH(@sText)-1)  

   END  
  ELSE  
   BEGIN  
   --One character remains.  
   --Insert the character, and exit the WHILE loop.  
   INSERT @retArray (idx, value)  
   VALUES (@idx, @sText)  
   SET @bcontinue = 0   
   END  
 END  
END  
RETURN  
END