September 20, 2016 at 11:25 am
We're storing some data from our Active Directory network into a table. There are 2 columns which are the most interesting. One is for the employee and the other is for the employee's supervisor, which is called ADManager. (That might be the name of the field in AD, I don't really know.) The issue is that the values that the extract from AD gives us is the full CN record from AD, but we have to only store just the employee's manager's name. For example, this is what is coming out of AD for a manager whose name is John Smith:
CN=John Smith,OU=Production,OU=IT,OU=Santa Fe,OU=Region 2,DC=doh,DC=state,DC=nm,DC=us
What we want to store in the ADManager field of the table is just "John Smith". My problem is that my SQL skills aren't up to retrieving just "John Smith" from the field. I know how to find such records using the wild card character %:
SELECT * FROM ADTable WHERE ADManager like 'CN=%,OU=%'
But that will just give me the whole record with the whole ADManager field. How do I pare it down to just "John Smith"?
BTW, in looking at the data in the ADTable, I've seen that the manager's name (and employee's name, too) is either like this:
FirstName LastName
Or it is like this:
FirstName.LastName
Although AD allows for this:
LastName, FirstName
our system admins must not be storing it like LastName, FirstName in AD, so I don't have to concern myself with the embedded comma.
Kindest Regards, Rod Connect with me on LinkedIn.
September 20, 2016 at 11:52 am
I would use CHARINDEX to look for the start and end of the section you need, and then use the substring function around that.
DECLARE @string VARCHAR(256) = 'CN=John Smith,OU=Production,OU=IT,OU=Santa Fe,OU=Region 2,DC=doh,DC=state,DC=nm,DC=us';
SELECT SUBSTRING(@string, CHARINDEX('CN=', @string) + 3,
CHARINDEX(',OU=', @string) - 4);
September 20, 2016 at 3:00 pm
What Nicholas proposed will work provided that CN= always comes before OU=. Working with AD, I've see that this is not always the case. If this is how it is in your environment then that's the way to go.
Provided you don't have CN=Lastname,Firstname in your table you could use DelimitedSplit8K_LEAD. Note the code below and my comments.
-- against a variable
DECLARE @string VARCHAR(256) = 'CN=John Smith,OU=Production,OU=IT,OU=Santa Fe,OU=Region 2,DC=doh,DC=state,DC=nm,DC=us';
SELECT details = REPLACE(details,'.',' ')
FROM
(
SELECT
ItemNumber,
SUBSTRING(Item, 1, CHARINDEX('=',item)-1),
SUBSTRING(Item, CHARINDEX('=',item)+1,100)
FROM DelimitedSplit8K_LEAD(@string, ',')
) parseAD(itemNumber, container, details)
WHERE container = 'CN'; -- this filter may need to be updated depending on how the data is stored
-- against a table
DECLARE @table TABLE (personID int, managerADInfo varchar(1000));
INSERT @table
VALUES
(1,'CN=John Smith,OU=Production,OU=IT,OU=Santa Fe,OU=Region 2,DC=doh,DC=state,DC=nm,DC=us'),
(2,'CN=Bill.Jones,OU=Production,OU=IT,OU=Chicago,OU=Region 2,DC=xxx,DC=state,DC=IL,DC=us');
SELECT
personID,
managerADInfo,
details = REPLACE(details,'.',' ')
FROM @table
CROSS APPLY
(
SELECT details
FROM
(
SELECT
ItemNumber,
SUBSTRING(Item, 1, CHARINDEX('=',item)-1),
SUBSTRING(Item, CHARINDEX('=',item)+1,100)
FROM DelimitedSplit8K_LEAD(managerADInfo, ',')
) parseAD(itemNumber, container, details)
WHERE container = 'CN'
) parseAD;
-- Itzik Ben-Gan 2001
September 20, 2016 at 3:23 pm
Borrowing from Alan's example, the same thing can be done w/o the split function. The split may be faster on huge datasets, but I think just using CROSS APPLY will perform OK for normal processing.
-- against a table
DECLARE @table TABLE (personID int, managerADInfo varchar(1000));
INSERT @table
VALUES
(1,'CN=John Smith,OU=Production,OU=IT,OU=Santa Fe,OU=Region 2,DC=doh,DC=state,DC=nm,DC=us'),
(2,'CN=Bill.Jones,OU=Production,OU=IT,OU=Chicago,OU=Region 2,DC=xxx,DC=state,DC=IL,DC=us'),
(3,'OU=Production,OU=IT,OU=Chicago,CN=Fred Dudely,OU=Region 2,DC=xxx,DC=state,DC=IL,DC=us');
SELECT t.*
, t4.ManagerName
FROM @table t
CROSS APPLY (SELECT CHARINDEX('CN=', t.managerADInfo)) t1(CN)
CROSS APPLY (SELECT t1.CN + 3) t2(NameStartPosition)
CROSS APPLY (SELECT CHARINDEX(',', t.managerADInfo, t2.NameStartPosition))t3(NameEndPosition)
CROSS APPLY (SELECT SUBSTRING(t.managerADInfo, t2.NameStartPosition, (t3.NameEndPosition - t2.nameStartPosition)))t4(ManagerName)
September 20, 2016 at 4:47 pm
I also don't see a need to split the entire string in this case, since you all want to extract is the CN= name.
SELECT
string,
CASE WHEN cn_start_byte = 0 THEN ''
ELSE SUBSTRING(string, cn_start_byte + 3, first_comma_after_cn - cn_start_byte - 3)
END AS ADManager
FROM (
VALUES('CN=John Smith,OU=Production,OU=IT,OU=Santa Fe,OU=Region 2,DC=doh,DC=state,DC=nm,DC=us'),
('OU=Production,CN=Jane Seymore,OU=IT,OU=Santa Fe,OU=Region 2,DC=doh,DC=state,DC=nm,DC=us'),
('OU=Anywhere,CN=Any name goes here') /* CN= is last entry */
) AS test_data(string)
CROSS APPLY (
SELECT CHARINDEX('CN=', string) AS cn_start_byte
) AS assign_alias_names1
CROSS APPLY (
SELECT CHARINDEX(',', string + ',', cn_start_byte + 3) AS first_comma_after_cn
) AS assign_alias_names2
Edit: Added "+ ','" to CA2, which I inadvertently left out of the original code.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply