-- Scalar Function template, provided by www.DynDeveloper.com
-- Please leave the line above in your code
IF EXISTS (SELECT *
FROM sys.objects
WHERE name = N'ddf_CommentTags')
DROP FUNCTION ddf_CommentTags
GO
CREATE FUNCTION ddf_CommentTags (@Comment AS VARCHAR(MAX))
--to test this, run this script:
-- SELECT dbo.ddf_CommentTags(' this is a message to @sgreen and @bsmith about nothing') as emailList
Returns VARCHAR(MAX)
AS
begin
declare @EmailList VARCHAR(MAX) = ''
--DECLARE @comment VARCHAR(MAX) = '@steve please check with @john and @missie today';
DECLARE @pos INT = 1;
DECLARE @start INT;
DECLARE @end INT;
DECLARE @email VARCHAR(100);
WHILE 1 = 1
BEGIN
-- Find next @
SET @start = CHARINDEX('@', @comment, @pos);
IF @start = 0 BREAK; -- no more tags
-- Must have space before @ OR @ is first character
IF @start > 1 AND SUBSTRING(@comment, @start - 1, 1) <> ' '
BEGIN
SET @pos = @start + 1;
CONTINUE;
END
-- Must NOT have space after @
IF SUBSTRING(@comment, @start + 1, 1) = ' '
BEGIN
SET @pos = @start + 1;
CONTINUE;
END
-- Find end of username (space or end of string)
SET @end = CHARINDEX(' ', @comment, @start + 1);
IF @end = 0 SET @end = LEN(@comment) + 1;
-- Extract username
SET @email = SUBSTRING(@comment, @start + 1, @end - @start - 1);
-- Clean up punctuation if needed
SET @email = REPLACE(@email, '@', '');
SET @email = REPLACE(@email, ',', '');
SET @email = REPLACE(@email, '.', '');
--sometimes they put '@' as 'as per tim @ cornerstone'
IF LEN(@email) > 2 BEGIN
-- Apply domain rule
SET @email = @email + '@mydomain.com';
IF CHARINDEX(@email,@emaillist) = 0 BEGIN
SET @EmailList += @email + ';'
end
end
-- Move search position forward
SET @pos = @end;
END
RETURN (@EmailList)
end
go
grant exec on ddf_CommentTags to PUBLIC