Getting the E-mail name from Windows Active Directory (AD) code

Getting the E-mail name from Windows Active Directory (AD) code.

Recently I needed to get the e-mail address for a person to be outputted on a SSRS report. The user table did not have the actual address and the name would not be possible to concatenate somehow. What did I have? It was only the long Active Directory (AD) data. How can I parse through this to get the name and concatenate on the address for this report? We will want to use T-SQL to extract the “CN” which stands for “Common Names”.

Please walk through this example with me!

Say you have the field “distinguished Name” in your table with:

DistinguishedName
------------------------
CN=joe.smith,OU=factory,OU=Email,DC=companynet,DC=company,DC=com

The code you can use to extract ”joe.smith” and concatenate ‘@company.com” is:

SELECT SUBSTRING((LEFT(U.DistinguishedName, CHARINDEX(',', U.DistinguishedName + ',') -1)),4,LEN((LEFT(U.DistinguishedName, CHARINDEX(',', U.DistinguishedName + ',') -1)))) + '@company.com’ AS "Email Address"

FROM (table)

Your result will equal:

Email Address
-----------------
joe.smith@company.com