DELIMITER $$

CREATE FUNCTION RemoveTrailingSlashes(data TEXT, startPosition INT) RETURNS TEXT
BEGIN
	set @start = locate('href', data, startPosition);
	
	if (@start > 0) then
		fixLoop: loop
		
			/* Find first quote */
			set @startIdx = @start;
			
			startQuoteLoop: loop
				if (substr(data, @startIdx, 1) = '"') then
					set @startQuoteIdx = @startIdx + 1;
					leave startQuoteLoop;
				else
					set @startIdx = @startIdx + 1;
					iterate startQuoteLoop;
				end if;			
			end loop startQuoteLoop;
			
			/* Find last quote */
			set @startIdx = @startQuoteIdx;
			
			endQuoteLoop: loop
				if (substr(data, @startIdx, 1) = '"') then
					set @endQuoteIdx = @startIdx;
					leave endQuoteLoop;
				else
					set @startIdx = @startIdx + 1;
					iterate endQuoteLoop;
				end if;						
			end loop endQuoteLoop;		
			
			/* Strip trailing slash if we need to */
			if (substr(data, @startQuoteIdx, 1) = '/') then
				set @url = substr(data, @startQuoteIdx, @endQuoteIdx - @startQuoteIdx);
				set @newUrl = if(length(@url) > 1 and substring(@url, length(@url), 1) = '/', substring(@url, 1, length(@url) - 1), @url);
				set data = replace(data, @url, @newUrl);
			end if;
			
			/* Find the next HREF */
			set @start = locate('href', data, @endQuoteIdx);
			
			if (@start = 0) then
				leave fixLoop;
			else		
				iterate fixLoop;
			end if;
						
		end loop fixLoop;
	end if;
	
	return data;
END$$

DELIMITER ;

/* Fix dress code */
update site_global
set data = RemoveTrailingSlashes(data, 1)
where label = 'dress_code';

/* Fix raceday info */
update site_global
set data = RemoveTrailingSlashes(data, 1)
where label = 'raceday_info';

DROP FUNCTION IF EXISTS RemoveTrailingSlashes;