1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48
| CREATE TABLE Domain
(
_ID int NOT NULL IDENTITY
CONSTRAINT PK_Domain PRIMARY KEY
, DomainPath varchar(128)
)
GO
INSERT INTO dbo.Domain (DomainPath)
VALUES ('CN=MYCOMPUTER,OU=COMPUTERLEVEL,OU=INTERMEDIATE,OU=TOPLEVEL,DC=my,DC=domain,DC=net')
GO
CREATE FUNCTION maFonction (@sz varchar(128))
RETURNS TABLE
AS
RETURN
(
SELECT RIGHT(S.sz, l) AS first_ou
, SUBSTRING(S.sz, f + 2, LEN(S.sz) - l - f - 2) AS second_ou
, LEFT(S.sz, f) AS third_ou
FROM (
SELECT S.sz
, CHARINDEX('\', S.sz) - 1
, CHARINDEX('\', REVERSE(S.sz)) - 1
FROM (
SELECT RIGHT(S.sz, LEN(S.sz) -1)
FROM (
SELECT REPLACE(S.sz, '<OU>', '\')
FROM (
SELECT SUBSTRING
(
S.sz
, CHARINDEX('<', S.sz)
, LEN(S.sz) - CHARINDEX('>', REVERSE(S.sz)) - 4
)
FROM (
SELECT REPLACE(@sz, ',OU=', '<OU>') AS sz
) AS S(sz)
) AS S(sz)
) AS S(sz)
) AS S(sz)
) AS S(sz, f, l)
)
GO
SELECT *
FROM dbo.Domain AS D
CROSS APPLY dbo.maFonction(D.DomainPath) AS F |
Partager