Wednesday, August 24, 2011

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:


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

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

FROM (table)

Your result will equal:

Email Address