Technical Article

Employee Email Addresses Function

,

This function provides easy email address creation from employee's LastName, First name.

Enjoy!!

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO
CREATE FUNCTION dbo.udf_MakeEmailAddress(@LastFirst varchar(50), @MakeEmailAddr BIT=0)
RETURNS VARCHAR(50)
AS
BEGIN
   /***
    *   Date:         4/23/2002
    *   Author:       <mailto:mikemcw@4segway.biz>
    *   Project:      Employee Email Address Formatting
    *   Location:     Any user database
    *   Permissions:  PUBLIC EXECUTE
    *   
    *   Description:  Will return the employee's email address, IF
    *                  @MakeEmailAddr is 1; if not, then just the 
    *                  employee's FirstName LastName is returned.
    *
    *                  Best when an employee's actual address is like:
    *                  firstname.lastname@yourdomain.com
    *                  
    *   Usage Examples: 
    *         select dbo.udf_MakeEmailAddress(t.LastName + ',' + t.FirstName ,0) 
    *            AS EmailAddress FROM mytable t
    *
    *         Let's say the employee's [lastname,firstname] is in the database
    *            (notice the comma, it must be in the value passed to the function
    *         
    *            select dbo.udf_MakeEmailAddress(t.employeename ,0) 
    *               AS EmailAddress FROM mytable t
    *
    *         Simple example:
    *            select dbo.udf_MakeEmailAddress('LastName, FirstName',0)
    *               returns:  FirstName LastName
    *            select dbo.udf_MakeEmailAddress('LastName, FirstName',1)
    *               returns:  FirstName.LastName@YourDomain.com
    *
    *   Restrictions:  SQL Server 2000 Only
    *
    *   Make sure the ToDo's are done!
    * 
    ***/
   DECLARE @FirstLast  VARCHAR(50)
   DECLARE @DomainName VARCHAR(255) 

   --ToDo: Change this to your domain name, be sure to keep the @ sign
   SET @DomainName = '@YourDomain.com'

   IF (CHARINDEX(',', @LastFirst) > 0)  --make sure the @lastname is a person (no comma).
      BEGIN
      IF @MakeEmailAddr = 0 AND @LastFirst IS NOT NULL 
         SET @FirstLast = RTRIM(RIGHT(@LastFirst, CHARINDEX(',', REVERSE(@LastFirst)) -1)) + ' ' +
            RTRIM(LEFT(@LastFirst, CHARINDEX(',', @LastFirst)-1))
         ELSE
            SET @FirstLast = RTRIM(RIGHT(@LastFirst, CHARINDEX(',', REVERSE(@LastFirst)) -1)) + '.' +
               RTRIM(LEFT(@LastFirst, CHARINDEX(',', @LastFirst)-1))
      END
   ELSE
      --Just return the name passed in, we cannot do anything about it
      SET @FirstLast = @LastFirst 
       
   IF @MakeEmailAddr = 1 AND @LastFirst IS NOT NULL 
       SET @FirstLast = REPLACE(@FirstLast, ' ','') + @DomainName
        
   RETURN (LTRIM(RTRIM(@FirstLast)))
END
GO
GRANT EXECUTE ON dbo.udf_MakeEmailAddress TO PUBLIC
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating