Offhand, I would recommend normalizing this data if you have any intentions of searching for groups or similar. The reason being group xyz could be in the 3rd, 4th, or 5th position, making searching very difficult via this model.

Either way, though, you're looking at stripping the string down, determining the individual entries, then choosing what to do with them. If you were to normalize it, I'd recommend the following:

First, you want the delimitedSplit8k. Found here: Tally oh! article[/url]

Once you've got that, your code would look like this:

/*

DROP TABLE #tblADData

GO

CREATE TABLE #tblADData

(

RowID INT IDENTITY( 1,1),

DomainData nvarchar(2000)

)

insert into #tblADData values ('CN=James,OU=Luxembourg,OU=Italy,OU=Standard users,OU=Users,OU=Managed objects,DC=XYZ,DC=ABC Ltd,DC=fr')

insert into #tblADData values ('CN=Ernst,OU=Luxembourg,OU=Italy,OU=Standard users,OU=Users,OU=Managed objects,DC=XYZ,DC=ABC Ltd,DC=it')

insert into #tblADData values ('CN=Tommy,OU=Luxembourg,OU=Belgium,OU=Standard users,OU=Users,OU=Managed objects,DC=ABSD,DC=XYZ Ltd,DC=ne')

insert into #tblADData values ('CN=Dcruz,OU=Tuscany,DC=PQRST,DC=PQR Ltd,DC=PQRS')

insert into #tblADData values ('CN=Marti Ho,OU=Intl Contacts,OU=Japan,OU=Contacts,OU=Users,DC=ABSD,DC=ne')

insert into #tblADData values ('CN=Miranda,OU=Montreal,DC=PQR,DC=ABC Ltd,DC=ABSD')

insert into #tblADData values ('CN=Will Hay,OU=Local Contacts,OU=Paris,OU=Contacts,OU=Users,DC=XYZ,DC=fr')

*/

;WITH cte AS

(SELECT

*

FROM

#tblADData AS a

CROSS APPLY

dbo.DelimitedSplit8k( a.DomainData, ',') AS split

)

SELECT

STUFF( c1.Item , 1, 3, '') AS NameOfUser,

LEFT( c2.Item, 2) AS AttributeType,

STUFF( c2.Item, 1, 3, '') AS AttributeValue

FROM

cte AS c1

JOIN

cte AS c2

ONc1.RowID = c2.RowID

WHERE

LEFT( c1.Item, 2) = 'CN'

AND LEFT( c2.Item, 2) <> 'CN'

EDIT: Forgot to mention that you need to adjust your source table to have an easy row identifier.