ALTER TABLE [AKUZ].[T_HOSPITAL_DESTINATION_DICTIONARY] ADD [ScheduleElements] varchar(300) NULL
GO

declare @patindex varchar(50) = '%[0-2][0-9]:[0-5][0-9]%';

with cte as (
    select 
		dict.HospitalDestinationDictionaryID,
        dict.Signa,
        cast(substring(dict.Signa, patindex(@patindex, dict.Signa), 5) as varchar(max)) as receptionTime,
        stuff(dict.Signa, patindex(@patindex, dict.Signa), 5, '') as RemainingPart
    from akuz.T_HOSPITAL_DESTINATION_DICTIONARY dict
    where patindex(@patindex, dict.Signa) > 0 
	and dict.Signa not like '%N [0-9]%' 
	and dict.Signa not like '%Однократно%'
    and dict.Signa not like '%По графику до отмены%'
	and dict.Signa not like '%Доз(а) на 1 прием%' 
	and dict.Signa not like '%[0-2][0-9]:[0-5][0-9]%-%[0-2][0-9]:[0-5][0-9]%'
	and dict.Signa not like '%[0-2][0-9]:[0-5][0-9]%по%[0-2][0-9]:[0-5][0-9]%' 
	and dict.Signa not like '%[0-2][0-9]:[0-5][0-9]%до%[0-2][0-9]:[0-5][0-9]%'

    union all

    select 
		HospitalDestinationDictionaryID,
        signa,
        cast(substring(RemainingPart, patindex(@patindex, RemainingPart), 5) as varchar(max)),
        stuff(RemainingPart, patindex(@patindex, RemainingPart), 5, '') as RemainingPart
    from cte
    where patindex(@patindex, RemainingPart) > 0

)
select 
	HospitalDestinationDictionaryID,
    signa,
    stuff((select ';' + receptionTime
           from cte r
           where r.HospitalDestinationDictionaryID = c.HospitalDestinationDictionaryID
           for xml path(''), type).value('.', 'varchar(max)'), 1, 1, '') as times
into #temp
from cte c
group by HospitalDestinationDictionaryID, signa;

--select * from #temp

update dictionary
set dictionary.ScheduleElements = temptable.times
from akuz.T_HOSPITAL_DESTINATION_DICTIONARY dictionary
join #temp temptable on temptable.HospitalDestinationDictionaryID = dictionary.HospitalDestinationDictionaryID

drop table #temp
