Select a portion of an LDAP string in a SQL Query

1

Hello All,

I need to extract a piece of information from an LDAP string. In our AD structure, it is the actual site location identifier. I need to start at the end of the string and work backward as the location will always fall in the same position going in that direction.

CN=myhostname,OU=Server01,OU=Server,OU=mylocation,OU=region,OU=org,DC=mydomain,DC=contoso,DC=com

I am trying to extract "mylocation" with the query.

Thanks!

2

will this work?

declare @s varchar(200) = 'CN=myhostname,OU=Server01,OU=Server,OU=mylocation,OU=region,OU=org,DC=mydomain,DC=contoso,DC=com'

declare @t table (id int identity(1,1), 
				  LDAP varchar(100))
insert into @t
select * from string_split(@s, ',')

Select  * from @t
where ID = (select Max(ID) - 5 from @t)
3

string_split returns a table of rows in which each row contains one of the substrings, but they can be in any order. In practice this often works out to be in the expected order as the input but there is no guarantee. The documentation is here.

4

Hi Mike - The dBase is SQL 2014, appears the STRING_SPLIT is not introduced until 2016. I could not get it to work "Invalid object name 'string_split'" I cannot upgrade this dBase as it is running proprietary legacy applications for the company. Is a project planned for next year to get off these legacy applications and migrate.

5

so I assume my location is different for each user?
Look into DelimitedSplit8K.
In my case [SharedResources].[dbo].[vw_adUsers] is a view that selects users from LDAP. What you are showing looks like distinguishedName.

SELECT TOP (1000) [distinguishedName], a.*
  FROM [SharedResources].[dbo].[vw_adUsers]
  cross apply DelimitedSplit8K([distinguishedName], ',') a
6

Maybe this? But the minute your Sys Admin makes a whole sale change in AD, this will break. How do I know. It has happened to me very often. String parsing is very fragile.

/****** Script for SelectTopNRows command from SSMS  ******/
;with src
as
(
	select 'CN=myhostname,OU=Server01,OU=Server,OU=Seattle,OU=region,OU=org,DC=mydomain,DC=contoso,DC=com' as distinguishedName
	union
	select 'CN=myhostname,OU=Server01,OU=Server,OU=New York,OU=region,OU=org,DC=mydomain,DC=contoso,DC=com' as distinguishedName
	union
	select 'CN=myhostname,OU=Server01,OU=Server,OU=San Diego,OU=region,OU=org,DC=mydomain,DC=contoso,DC=com' as distinguishedName
	union
	select 'CN=myhostname,OU=Server01,OU=Server,OU=Asmera,OU=region,OU=org,DC=mydomain,DC=contoso,DC=com' as distinguishedName
	

)
SELECT TOP (1000) [distinguishedName], replace(a.Item,',OU','') as Location
  FROM src
  cross apply DelimitedSplit8K(distinguishedName, '=') a
  where ItemNumber = 5

image