SQL Parse string

DECLARE @Recurrence varchar(100)
SET @Recurrence = 'DTSTART:20110530T000000Z  DTEND:20110531T000000Z  RRULE:FREQ=WEEKLY;INTERVAL=1;BYDAY=MO,TH,SU'

SELECT SUBSTRING(@Recurrence, CHARINDEX('FREQ=', @Recurrence) + 5, CHARINDEX(';', @Recurrence) - CHARINDEX('FREQ=', @Recurrence) - 5) AS Freq,
    SUBSTRING(@Recurrence, CHARINDEX('INTERVAL=', @Recurrence) + 9, CHARINDEX(';', @Recurrence, CHARINDEX('INTERVAL=', @Recurrence)) - CHARINDEX('INTERVAL=', @Recurrence) - 9) AS Interval,
    SUBSTRING(@Recurrence, CHARINDEX('BYDAY=', @Recurrence) + 6, LEN(@Recurrence)) AS ByDay
WHERE @Recurrence LIKE '%FREQ=[A-Z]%;INTERVAL=[0-9]%;BYDAY=[A-Z]%'

Select allOpen in new window

SELECT SUBSTRING(Recurrence, CHARINDEX('FREQ=', Recurrence) + 5, CHARINDEX(';', Recurrence) - CHARINDEX('FREQ=', Recurrence) - 5) AS Freq,
    SUBSTRING(Recurrence, CHARINDEX('INTERVAL=', Recurrence) + 9, CHARINDEX(';', Recurrence, CHARINDEX('INTERVAL=', Recurrence)) - CHARINDEX('INTERVAL=', Recurrence) - 9) AS Interval,
    SUBSTRING(Recurrence, CHARINDEX('BYDAY=', Recurrence) + 6, LEN(Recurrence)) AS ByDay
FROM SomeTable
WHERE Recurrence LIKE '%FREQ=[A-Z]%;INTERVAL=[0-9]%;BYDAY=[A-Z]%'

Select allOpen in new window

CREATE FUNCTION [dbo].[ToKeyValuePairs]
(	
	@input nvarchar(4000),
	@keydelim nchar(1),
	@pairdelim nchar(1)
)
RETURNS @result TABLE ([Key] nvarchar(4000), [Value] nvarchar(4000))
AS
BEGIN
	declare @i int, @len int, @mode bit, @c nchar(1)
	declare @eval nvarchar(4000)
	declare @key nvarchar(4000)
	declare @value nvarchar(4000)

	set @len = len(@input) + 1
	set @input = @input + @pairdelim
	set @key = ''
	set @value = ''
	set @i = 1
	set @mode = 0 -- 0=key; 1=value

	while @i <= @len begin
		set @c = substring(@input, @i, 1)
		
		if @mode = 0 begin -- capturing key
			if @c <> @pairdelim
				if @c <> @keydelim
					set @key = @key + @c
				else
					set @mode = 1
		end else begin
			if @c <> @pairdelim
				set @value = @value + @c
			else begin
				set @mode = 0
				
				insert into @result values (@key, @value)
				
				set @key = ''
				set @value = ''
			end
		end
		
		set @i = @i + 1
	end
	
	return
END

Select allOpen in new window

declare @config varchar(4000)
declare @rrule varchar(4000)
declare @params table ([Key] varchar(4000), [Value] varchar(4000))
declare @freq varchar(10)
declare @interval varchar(10)
declare @byday varchar(10)

set @config = 'DTSTART:20110530T000000Z  DTEND:20110531T000000Z  RRULE:FREQ=WEEKLY;INTERVAL=1;BYDAY=MO,TH,SU'

select @rrule = [Value] 
  from dbo.ToKeyValuePairs(@config,':', ' ')
 where [Key] = 'RRULE'
 
insert into @params
	select [Key], [Value]
	  from dbo.ToKeyValuePairs(@rrule,'=', ';')
	  
select @freq = [Value] from @params where [Key] = 'FREQ'
select @interval = [Value] from @params where [Key] = 'INTERVAL'
select @byday = [Value] from @params where [Key] = 'BYDAY'

select @freq, @interval, @byday

Select allOpen in new window

---------- ---------- ----------
WEEKLY     1          MO,TH,SU

Select allOpen in new window