Network Programming in .NET

This is a nice little user defined function that can extract email addresses from a block of text. I got the split function from planet-source-code, just to give credit where due.

 

CREATE FUNCTION dbo.Split(@String nvarchar(4000), @Delimiter char(1))
RETURNS @Results TABLE (Items nvarchar(4000))
AS

    BEGIN
    DECLARE @INDEX INT
    DECLARE @SLICE nvarchar(4000)
    — HAVE TO SET TO 1 SO IT DOESNT EQUAL Z
    —     ERO FIRST TIME IN LOOP
    SELECT @INDEX = 1
    — following line added 10/06/04 as null
    —      values cause issues
    IF @String IS NULL RETURN
    WHILE @INDEX !=0

        BEGIN 
         — GET THE INDEX OF THE FIRST OCCURENCE OF THE SPLIT CHARACTER
         SELECT @INDEX = CHARINDEX(@Delimiter,@STRING)
         — NOW PUSH EVERYTHING TO THE LEFT OF IT INTO THE SLICE VARIABLE
         IF @INDEX !=0
          SELECT @SLICE = LEFT(@STRING,@INDEX – 1)
         ELSE
          SELECT @SLICE = @STRING
         — PUT THE ITEM INTO THE RESULTS SET
         INSERT INTO @Results(Items) VALUES(@SLICE)
         — CHOP THE ITEM REMOVED OFF THE MAIN STRING
         SELECT @STRING = RIGHT(@STRING,LEN(@STRING) – @INDEX)
         — BREAK OUT IF WE ARE DONE
         IF LEN(@STRING) = 0 BREAK
    END
    RETURN
END

CREATE FUNCTION get_email
 (  @TextContainingEmail varchar(1000)  )
RETURNS varchar(1000)
AS
BEGIN
 declare @retval varchar(1000)
 select top 1 @retval=items from dbo.split(@TextContainingEmail,’ ‘)
 where items like ‘%@%’
 return @retval
END

 

Then to use it, you can call

 

select dbo.get_email(‘my email address is bob@microsoft.com’‘)