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
