Home > Uncategorized > Extract email address with SQL UDF

Extract email address with SQL UDF

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’)
 
 
Advertisements
Categories: Uncategorized
  1. Joshua
    October 16, 2010 at 9:36 pm

    Hi,

    Thanks for the code. BTW this will crash the moment you leave space (delimiter) at the end of the text on line RIGHT(@STRING,LEN(@STRING) – @INDEX)

    J

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: